Optional parameters in Merge

Question

I have a stored procedure used for updating records in a SQL Server database.

I want to use the existing value within the database if a match is found and a null is provided as the variable.

Often only partial data is available so the procedure is executed at several stages to complete the record. I do not always have all variables at each execution.

Why is the below CASE not working? If a null value is provided, it replaces existing data within the field.

ALTER PROCEDURE [dbo].[sp_UPSERT_Test] 

@ID int,
@Account_ID varchar(15)
@Name varchar(15)

AS 
BEGIN 

MERGE INTO dbo.Database_Log AS target  
USING ( 
   SELECT @ID, @Account_ID ,@Account_ID)  
   AS source (ID, Account_ID, Name)
ON target.ID = source.ID 

WHEN MATCHED
THEN UPDATE
SET
    Account_ID = CASE 
    WHEN source.Account_ID = NULL 
    THEN target.Account_ID 
    ELSE source.Account_ID 
        END
    ,Name = CASE 
    WHEN source.Name = NULL 
    THEN target.Name 
    ELSE source.Name 
        END)

WHEN NOT MATCHED  
THEN INSERT (Account_ID, Name)  
       VALUES (Account_ID, Name);

RETURN
END

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 17 '13 at 10:03

score 1 · Accepted Answer · answered Feb 17 '13 at 10:04

Maybe because you're not properly checking for NULL ? NULL is not a value you can compare with the equality operator - you must use IS NULL

Try this:

 SET      
    Account_ID = CASE 
                    WHEN source.Account_ID IS NULL 
                    THEN target.Account_ID 
                    ELSE source.Account_ID 
                 END

Use IS NULL instead of = NULL

Optional parameters in Merge

1 Answers1