0

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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
Rosko
  • 1
  • I *hope* you are properly quoting the value of `@NameTB` before you inject it into your dynamic statement. If not, this is very likely wide open to injection attacks. – Thom A Mar 10 '21 at 11:46
  • 2
    Why would you hardcode the column names of a table but not the name of the table itself? This looks like a schema problem - one shouldn't have multiple tables containing the same columns. And why would such a table allow NULL for a column whose name sounds like an identifier? – SMor Mar 10 '21 at 12:10
  • I would *guess* it's a foreign key, @SMor . – Thom A Mar 10 '21 at 12:16
  • Let's see the table schema, and lets' see how you declare those variables – Charlieface Mar 10 '21 at 12:33
  • The table name is built inside the stored vine and is not passed externally by a parameter, I have several identical tables divided for years to speed up the query. But the problem is another if somehow with sp_executesql it is possible to update a column by setting the value to null – Rosko Mar 10 '21 at 12:58

1 Answers1

2

= 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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786