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.
This question is related to
sql
tsql
stored-procedures
sql-update
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'
Source: Stackoverflow.com