'Mssql loop with UNION t-sql

I just use Mssql procedure given at this site Using loop with UNION t-sql to build my own loop procedure.
When I'm executing this code:

SELECT      '[hpsdb].[dbo].[' + r.CAMP_TABLE_NAME + ']' 
FROM        [hpsdb].[dbo].[T_HPS_CAMPAIGN_TASK] t
LEFT JOIN   [hpsdb].[dbo].[T_HPS_CAMPAIGN_TABLE_REF] r ON (r.CAMPAIGN_ID=t.CAMPAIGN_ID)
WHERE       SKILL_ID = 2

it is returning two tables name. But when I executing this procedure it is never end.

DECLARE @TableName VARCHAR(100)
DECLARE @DSQL VARCHAR(4000)
SET @DSQL = ''

DECLARE cTableList CURSOR FOR
SELECT      '[hpsdb].[dbo].[' + r.CAMP_TABLE_NAME + ']' 
FROM        [hpsdb].[dbo].[T_HPS_CAMPAIGN_TASK] t
LEFT JOIN   [hpsdb].[dbo].[T_HPS_CAMPAIGN_TABLE_REF] r ON (r.CAMPAIGN_ID=t.CAMPAIGN_ID)
WHERE       SKILL_ID = 2

OPEN cTableList

FETCH NEXT FROM cTableList INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DSQL = @DSQL + 'SELECT ID FROM ' + @TableName + ' UNION ALL '
END

CLOSE cTableList
DEALLOCATE cTableList

-- Remove the last UNION ALL
IF LEN(@DSQL) > 11 SET @DSQL = LEFT(@DSQL,LEN(@DSQL) - 11)

EXEC (@DSQL)
sqlsql-serverloops


Solution 1:[1]

You need another FETCH NEXT statement within your WHILE LOOP to keep moving through the cursor otherwise you will just stay on the first record and the value of @@FETCH_STATUS will never change.

FETCH NEXT FROM cTableList INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @DSQL = ''
      SET @DSQL = 'SELECT ID FROM [' + @TableName + ']'
    ELSE
      SET @DSQL = @DSQL + ' UNION ALL SELECT ID FROM [' + @TableName + ']'
    FETCH NEXT FROM cTableList INTO @TableName
END

Solution 2:[2]

FETCH NEXT FROM cTableList INTO @TableName must be used within loop. otherwise @@fetch_status will remain 0 which will cause infinity loop

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 HAROON rASHID

Related Questions