27

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.

Curtis
  • 101,612
  • 66
  • 270
  • 352
Jake
  • 273
  • 1
  • 3
  • 4

3 Answers3

55

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.

James Hill
  • 60,353
  • 20
  • 145
  • 161
  • 10
    That easy huh? Well I'm gonna go stick a fork in an electrical socket and score a dirty needle from a back alley in Atlantic City and other equally dumb things. Thank you for your help. – Jake Aug 18 '11 at 18:06
  • 1
    @Jake, No worries. We all have our days. BTW, welcome to StackOverflow! – James Hill Aug 18 '11 at 18:27
29

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])
Adam Caviness
  • 3,424
  • 33
  • 37
  • 7
    This is what I was beating my head into the desk trying to do. Much nicer looking than a whole bunch of "if-update" pairs when there's a dozen column involved. – Herb May 17 '12 at 13:51
5

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.

VladimirK
  • 27
  • 6
Thomas Hansen
  • 61
  • 1
  • 2