Dynamic SQL in Cursor

Anonymous
2021-01-29T15:50:48.517+00:00

Hi,

I have the below code, and I need to keep the result in the temp table. When I add Insert into temp table is extracting nothing while without it we have 7 records, and 7 records result is the correct result.
Could you please help me what is the problem with my code with the temp table?

DECLARE @COL nvarchar(255), @CMD nvarchar(max)
DECLARE @Results as TABLE (ResultText VARCHAR(500));

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = N'Map'

OPEN getinfo
FETCH NEXT FROM getinfo into @COL
WHILE @@Fetch _STATUS = 0
BEGIN

SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM Map WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'  
INSERT INTO @Results  
EXEC SP_EXECUTESQL @cmd  
FETCH NEXT FROM getinfo into @col  

END
CLOSE getinfo
DEALLOCATE getinfo

select * from @Results

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2021-01-29T16:26:31.973+00:00

    You can't use dynamic SQL to insert into a table variable in the outer block, it has to be a temp table, you need to put the "INSERT INTO #RESULTS" in the @CMD variable, and your @CMD statement must do a SELECT, not a PRINT. So you want

    DECLARE @col nvarchar(255), @cmd nvarchar(max)  
    Create Table #Results  (ResultText VARCHAR(500));  
    set nocount on  
    DECLARE getinfo cursor for  
    SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID  
    WHERE t.Name = N'Map'  
      
    OPEN getinfo  
    FETCH NEXT FROM getinfo into @col  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
         SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM Map WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #Results select ''' + @col + ''' end'  
         EXEC SP_EXECUTESQL @cmd  
         FETCH NEXT FROM getinfo into @col  
      
    END  
    CLOSE getinfo  
    DEALLOCATE getinfo  
      
    select * from #Results  
    

    Tom

    1 person found this answer helpful.

  2. Tom Phillips 17,716 Reputation points
    2021-01-29T17:12:43.007+00:00

    Just to be clear.

    Your problem is your @CMD does not return a result set, it prints to the screen.

    Change it to:
    SELECT @CMD = 'IF NOT EXISTS (SELECT top 1 * FROM Map WHERE [' + @COL + '] IS NOT NULL) BEGIN SELECT ''' + @COL + ''' end'

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2021-02-01T05:55:25.553+00:00

    Hi anonymous user,

    As mentioned by other experts, you need to replace 'PRINT' with 'SELECT' in your statement otherwise it could be no output in your temp table.

    Besides, you could use below queries to list all the columns of one table.

    --Method1  
    SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Map')   
      
    --Method2  
    SELECT COLUMN_NAME name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Map'  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments