CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-update
Harshil Doshi
Harshil Doshi

Reputation: 3592

Update only if the new value is not Zero

I've written an stored procedure which updates the fields based on the values passed in parameters.

Stored Procedure

CREATE PROCEDURE T1 
( 
@a INTEGER,  
@b INTEGER,    
@c nvarchar(max), 
@d nvarchar(max)
)  
AS  
BEGIN 
UPDATE Feed
Set b=@b,
    c=ISNULL(@c,c), 
    d=ISNULL(@d,d),     
where a = @a    
end

Now it is very common that we may need to update only some of the fields at a time. Suppose I only want to update c. Now in this case, parameters b & d should be Null. Hence, I am handling that with IsNull().

But, the thing is that @b is an int and hence it's passing as 0 instead of Null. How to handle this update.

If the value passed is 0 then that column should not be updated.

Note: There won't be any case where my int columns will actually be 0.

Upvotes: 1

Views: 1889

Answers (2)

Ezequiel
Ezequiel

Reputation: 1

As said the previous comment you can use CASE WHEN... but I prefer use IIF(@b = 0, b, @b) to be more clean

CREATE PROCEDURE T1(
    @a INTEGER,
    @b INTEGER,
    @c nvarchar(max),
    @d nvarchar(max)
)
AS
BEGIN
    UPDATE Feed
    Set b= IIF(@b = 0, b, @b),
        c=ISNULL(@c, c),
        d=ISNULL(@d, d)
    WHERE a = @a
end

the docs:

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-ver16#syntax

Upvotes: 0

Alexander Volok
Alexander Volok

Reputation: 5940

It can be done via CASE within UPDATE:

    CREATE PROCEDURE T1 
    ( 
    @a INTEGER,  
    @b INTEGER,    
    @c nvarchar(max), 
    @d nvarchar(max)
    )  
    AS  
    BEGIN 
    UPDATE Feed
    Set b=case when @b = 0 then b else @b end,
        c=ISNULL(@c,c), 
        d=ISNULL(@d,d),     
    where a = @a    
    end

Upvotes: 2

Related Questions