optional parameters in a SP

  • 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!

  • 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.

  • set Col2 = IsNull(@2,Col2),

    Col3 = IsNull(@3,Col3)

    where Col1 = @1

  • 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