Dynamic SQL in Cursor

Question

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

Answer 1

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

Answer 2

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'

Answer 3

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.