I have an update statement in a stored procedure that looks generally like this:
Update [TABLE_NAME]
Set XYZ=@ABC
Is there a good way to only trigger the update statement if the variable is not null or the value -1?
Similar to an IF NOT EXISTS...INSERT question.
Thank you so much.
Use a T-SQL IF:
IF @ABC IS NOT NULL AND @ABC != -1
UPDATE [TABLE_NAME] SET XYZ=@ABC
Take a look at the MSDN docs.
Another approach when you have many updates would be to use COALESCE:
UPDATE [DATABASE].[dbo].[TABLE_NAME]
SET
[ABC] = COALESCE(@ABC, [ABC]),
[ABCD] = COALESCE(@ABCD, [ABCD])
Yet another approach is ISNULL().
UPDATE [DATABASE].[dbo].[TABLE_NAME]
SET
[ABC] = ISNULL(@ABC, [ABC]),
[ABCD] = ISNULL(@ABCD, [ABCD])
The difference between ISNULL and COALESCE is the return type. COALESCE can also take more than 2 arguments, and use the first that is not null. I.e.
select COALESCE(null, null, 1, 'two') --returns 1
select COALESCE(null, null, null, 'two') --returns 'two'
One little note, if COALESCE hasn't argument that is not the NULL, it will throw an exception, so if you have just two arguments and both can be null - do use ISNULL.
