Update a null column in sql with spexecutesql

Question

I encountered a problem in the following query. When I go to update the column ID_Premesso with the value NULL the value does not change, while if I go to update with the value 0 it works.

    SET @SQL = 'UPDATE ' + @NameTB + ' SET ID_Permesso = NULL WHERE ID_Timbratura = @IDTimbratura ' 
    SET @PARAMS = '@IDTimbratura INT'
    EXECUTE sp_executesql @SQL, @PARAMS, @IDTimbratura;

Answer 1

= does not work for NULL values. So, if ID_Timbratura can be NULL, you need to take that into account:

    SET @SQL = '
UPDATE ' + @NameTB + '
    SET ID_Permesso = NULL
    WHERE ID_Timbratura = @IDTimbratura OR
          (ID_Timbratura IS NULL AND @IDTimbratura IS NULL)' ;

    SET @PARAMS = N'@IDTimbratura INT';

    EXECUTE sp_executesql @SQL, @PARAMS, @IDTimbratura;

Note that SQL Server lets you break strings over multiple lines. I reformatted the query string so it is easier to read.