Retain original value in DataBase when NULL value is passed to Stored Procedure

Question

Hi I am a newbie in SQL and require help I have a parameterized stored procedure which contains a Update query like...

UPDATE sometable 
SET 
 price1 = @param1,
 price2 = @param2,
 price3 = @param3,
 price4 = @param4,
WHERE
 ID = @param5

Now when I execute this SP by setting any of the parameters value as NULL it gets updated in DB, what I want to know is if one of the parameters value is NULL then can we retain that columns original value in DB instead of updating it with NULL.

Answer 1

Try wrapping the @paramx with a CASE statement:

CASE @paramx WHEN NULL THEN pricex ELSE @paramx END

Answer 2

In SQLServer the tidy way is to use ISNULL(@param1, price1).

This takes @param1 and checks if it is NULL. If it is NULL it is replaced with the value from price1. I like ISNULL as it's very readable, does what it says on the tin.

It's not ANSI SQL though, with a more flexible command existing there: COALESCE. It's just like ISNULL, but takes any number of parameters. It searches through them in order for the first non-NULL value. If none are found, NULL is returned...

UPDATE sometable 
SET 
        price1 =        ISNULL(@param1, price1),
        price2 =        ISNULL(@param2, price2),
        price3 =        ISNULL(@param3, price3),
        price4 =        ISNULL(@param4, price4)
WHERE
        ID = @param5

UPDATE sometable 
SET 
        price1 =        COALESCE(@param1, price1),
        price2 =        COALESCE(@param2, price2),
        price3 =        COALESCE(@param3, price3),
        price4 =        COALESCE(@param4, price4)
WHERE
        ID = @param5

Also see: "COALESCE vs ISNULL"