CODEWITHSUNDEEP
sqlt-sqlstored-proceduressql-update
Jake
Jake

Reputation: 283

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.

Upvotes: 28

Views: 127361

Answers (3)

Thomas Hansen
Thomas Hansen

Reputation: 61

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.

Upvotes: 5

Adam Caviness
Adam Caviness

Reputation: 3484

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])

Upvotes: 29

James Hill
James Hill

Reputation: 61872

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.

Upvotes: 57

Related Questions