SQL Server UPDATE: set column if parameter is not null

Question

I have a stored procedure that is meant to insert or update to a table depending on whether or not a value exists for a column. This is working fine except I only want to set the value of certain columns if a parameter has been passed. This is because I don't want to override the existing column value with null.

Below is an the idea of the stored procedure:

CREATE PROCEDURE [dbo].[p_SaveState]
   @Foo             nvarchar(50),
   @Bar             nvarchar(20) = null,
   @Something       nvarchar(20) = null
AS
BEGIN
   IF EXISTS (SELECT TOP(1) Foo FROM dbo.[State] WHERE Bar = @Bar)
   BEGIN
       UPDATE [dbo].[State]
       SET Bar = @Bar, 
           Something = @Something
       WHERE (--condition--)
   END
   ELSE
   BEGIN
      ... -- INSERT statement goes here

I am trying to accomplish something like this:

UPDATE [dbo].[State]
SET Bar = @Bar, 
    IF (@Something IS NOT NULL) Something = @Something
 WHERE (--condition--)

But I don't believe I can use CASE because I don't have the original value to set without performing a select.

How can I achieve this affect?

Answer 1

ISNULL will work fine here:

UPDATE [dbo].[State]
SET Bar = @Bar, 
    Something = ISNULL(@Something,Something)
WHERE...

Answer 2

COALESCE will do it for you!

UPDATE [dbo].[State]
SET Bar = @Bar, 
    Something = COALESCE(@Something,Something)
WHERE (--condition--)

Answer 3

You could use CASE, like so...

UPDATE [dbo].[State]
SET Bar = @Bar, 
    Something = CASE WHEN @Something IS NOT NULL THEN @Something ELSE Something END
WHERE (--condition--)

This will set the value of Something to your variable value if it's NOT NULL, but will use the original value if it is NULL.

The same can be achieved more concisely using ISNULL though, like so:

UPDATE [dbo].[State]
SET Bar = @Bar, 
    Something = ISNULL(@Something, Something)
WHERE (--condition--)