April 16, 2003 at 10:38 am
Is there any way to have optional parameters in a SP that does an update.
Lets say that I have 3 input params @1,@2 = NULL,@3 = NULL
2 and 3 default to NULL
the update is
Update mytable set 2=@2,3=@3 where 1 = @1
I only want the set for 2 and 3 to be done if 2 and 3 are actually passed in. So if I get 2 and not 3 I want this to execute
Update mytable set 2=@2 where 1 = @1
etc. Thanks!
April 16, 2003 at 11:05 am
One alternative is as follows
Update mytable
set Col2 = CASE
WHEN @2 IS NULL THEN Col2
ELSE @2
END,
Col3 = CASE
WHEN @3 IS NULL THEN Col3
ELSE @3
END
where Col1 = @1
(I have added col prefix just not to confuse this with number 2)
Hope this helps.
April 16, 2003 at 3:05 pm
set Col2 = IsNull(@2,Col2),
Col3 = IsNull(@3,Col3)
where Col1 = @1
April 17, 2003 at 6:12 am
Thanks, it worked great!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply