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


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.


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"


Try wrapping the @paramx with a CASE statement:

CASE @paramx WHEN NULL THEN pricex ELSE @paramx END

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »