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.
Let's look at an example of how to use the IS NOT NULL condition in a SELECT statement in SQL Server. For example: SELECT * FROM employees WHERE last_name IS NOT NULL; This SQL Server IS NOT NULL example will return all records from the employees table where the last_name does not contain a null value.
Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them.
A single stored procedure can be used to select, add, update, and delete data from a database table.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With