Keep original value in DataBase when NULL passed to stored procedure

Question

Hi, I am new to SQL and need help. I have a parameterized stored procedure that contains an Update query, for example ...

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

Now, when I run this SP, setting any parameter value as NULL, it is updated in the database, I want to know if one of the parameter values ​​is NULL, then we can save the original value of the columns in the database instead of updating it with NULL.

Answer 1

In SQLServer, a neat way is to use ISNULL (@ param1, price1) .

@param1 , NULL. NULL, 1. ISNULL, , , .

ANSI SQL, , : COALESCE. ISNULL, . , NULL. , NULL...

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

.: " COALESCE vs ISNULL"

Answer 2

@paramx CASE:

CASE @paramx    NULL THEN pricex   ELSE @paramx END