Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Stored Procedure: If variable is not null, update statement

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.

like image 600
Jake Avatar asked Aug 18 '11 17:08

Jake


People also ask

IS NOT NULL in SQL stored procedure?

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.

Can we update NULL value in SQL?

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.

Can we use update in stored procedure?

A single stored procedure can be used to select, add, update, and delete data from a database table.


3 Answers

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.

like image 145
James Hill Avatar answered Sep 20 '22 06:09

James Hill


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]) 
like image 33
Adam Caviness Avatar answered Sep 20 '22 06:09

Adam Caviness


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'
like image 39
Thomas Hansen Avatar answered Sep 22 '22 06:09

Thomas Hansen