Tutorial :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.


Solution:1

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"


Solution:2

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
Previous
Next Post »