Update null value to a column in dynamic SQL

Question

I need to update a specific set of columns with null value, but when I'm trying to pass null value to dynamic SQL I'm not getting any error or output.

DECLARE @Value VARCHAR(100)

SELECT @Value = null
            

DECLARE @TableName VARCHAR(1000),@ColumnName VARCHAR(100)

DECLARE @Sql NVARCHAR(MAX)
SET @Sql= N''

DECLARE UpdatePlantId_Crsr CURSOR

    STATIC FOR          

SELECT ST.name AS TableName,SC.name AS ColumnName 
FROM 
    sys.columns SC
INNER JOIN
    sys.tables ST ON ST.object_Id = SC.Object_Id
WHERE
    SC.name like '%_MLP'
    --AND ST.name not like 'tPlant'
                         
    OPEN UpdatePlantId_Crsr

    IF @@CURSOR_ROWS > 0

    BEGIN

    FETCH NEXT FROM UpdatePlantId_Crsr INTO @TableName,@ColumnName

    WHILE @@FETCH_STATUS = 0

    BEGIN


    SET @Sql= N''
    SELECT @Sql = @Sql + N' UPDATE  '+@TableName +' SET '+@ColumnName+ '= '+ @Value +'  
    '
    PRINT @Sql

        --EXEC(@Sql)

      FETCH NEXT FROM UpdatePlantId_Crsr INTO @TableName,@ColumnName

    END

    END

    CLOSE UpdatePlantId_Crsr

    DEALLOCATE UpdatePlantId_Crsr

. . Your code is clearly SQL Server so I added the tag. – Gordon Linoff May 05 '21 at 12:05 — Gordon Linoff, May 05 '21 at 12:05

score 0 · Answer 1 · answered May 05 '21 at 12:04

I suspect that something is wrong with your application if you need to use dynamic SQL to set tables and columns to NULL. That said, the question can still be answered.

You should use sp_executesql. But given that the table and column names cannot be passed in, just set up the SQL correctly:

SET @Sql = N'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = NULL'  ;

EXEC sp_executesql @sql;  -- yuo can still use it with no parameters

I am not sure why you are concatenating the @sql string, so I removed that.

Note that + NULL returns NULL -- both for string concatenation and addition.

Update null value to a column in dynamic SQL

1 Answers1