How to save print variable into another variable in SQL Server?

Question

Hi

Here is my script grab from net

declare @col varchar(255), @cmd varchar(max)
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 = 'tblDeductions' 
    AND c.name like 'fld%name' OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM tblDeductions WHERE [' 
            + @col + '] IS NOT NULL) BEGIN print ''
          ' + @col + ''' end' exec (@cmd)
        FETCH NEXT FROM getinfo into @col
END CLOSE getinfo
DEALLOCATE getinfo

The above query will display column names that are null values. But I need to save them into another variable with comma separated values.

Thanks in advance.

Answer 1

This should work, although there's almost certainly a more efficient way to do what you're trying to do.

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t

CREATE TABLE #t
(columnName VARCHAR(255))

DECLARE @col VARCHAR(255), @cmd VARCHAR(MAX)
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 = 'tblDeductions' 
    AND c.name LIKE 'fld%name' OPEN getinfo
FETCH NEXT FROM getinfo INTO @col
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM tblDeductions WHERE [' 
            + @col + '] IS NOT NULL) BEGIN INSERT #t VALUES (''' + @col + ''') end' 
          EXEC (@cmd)
        FETCH NEXT FROM getinfo INTO @col
END CLOSE getinfo
DEALLOCATE getinfo

DECLARE @columnsWithNull VARCHAR(MAX)
SET @columnsWithNull = (SELECT columnName + ','
                       FROM #t FOR  XML PATH(''))

SET @columnsWithNull = LEFT(@columnsWithNull,LEN(@columnsWithNull) - 1)

SELECT @columnsWithNull