SQL Stored Procedure: If variable is not null, upd

问题:

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.

回答1:

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.

回答2:

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

回答3:

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'