CODEWITHSUNDEEP
sqlasp.net.netsql-server-2012
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

Update row field only when the parameter been supplied with a value (SQL)

I've a stored procedure like this

alter procedure sp_test
@p1 varchar(10)='',
@p2 int=0,
@p3 nvarchar(10)=N''
as
begin
    update mytable set p1=@p1,p2=@p2,p3=@p3 where mycondition
end

I'm calling this SP from .NET and if am not supplying parameter say p2 in one call, this will obviously replace the p2 cell with empty string. So how can we avoid that. Like if no value supplied for a field use the existing value in that column. That way? The number of columns in that table is very huge and I thought its not a good practice to have separate update query for different situation.

Upvotes: 0

Views: 83

Answers (3)

Sandeep Thomas
Sandeep Thomas

Reputation: 4759

Sorry Ive got an answer for my own question, sharing it with those who needs the same

I modified the SP like this

alter procedure sp_test
@p1 varchar(10)=NULL,
@p2 int=NULL,
@p3 nvarchar(10)=NULL
as
begin
    update mytable set p1=COALESCE(@p1,p1),p2=COALESCE(@p2,p2),p3=COALESCE(@p3,p3) where mycondition
end

So COALESCE did the trick and solved the issue I think. So in my tests if no value

Upvotes: -1

tlt
tlt

Reputation: 15261

ALTER PROCEDURE sp_test
(
    @p1 varchar(10) = NULL,
    @p2 int = NULL,
    @p3 nvarchar(10) = NULL
)
AS
BEGIN
    UPDATE mytable 
    SET set p1= ISNULL(@p1, p1),
    p2 = ISNULL(@p2, p2),
    p3 = ISNULL(@p3, p3)
    WHERE mycondition
END

Upvotes: 2

Rokuto
Rokuto

Reputation: 814

You can use CASE ... WHEN ... e. g.

alter procedure sp_test
@p1 varchar(10)='',
@p2 int=0,
@p3 nvarchar(10)=N''
as
begin
    update mytable 
set 
p1=CASE WHEN @p1 IS NULL THEN p1 ELSE @p1 END,
p2= CASE WHEN @p2 IS NULL THEN p2 ELSE @p2 END,
p3= CASE WHEN @p3 IS NULL THEN p3 ELSE @p3 END 
where mycondition
    end

Also you can use COALESCE or ISNULL in SQL Server.

Upvotes: 1

Related Questions