Post reply

Update statement in a parameterized stored procedure

  • February 14, 2009 at 1:52 pm

    #94772

    I have a update statement with case statements within it. Here is the example:

    update test

    set col1 = (case when col1='Paul' then'p'

    when col1='Shawn'then 's'

    when col1='Mike' then 'm' else col1 end)

    Note: Since it is a global change. Same update statement will be used for 20 different projects/ table.

    I would like to make it a parameterized stored procedure where i can pass the table name as the parameter and the update is done.

    Help!!

    Thanks

  • February 15, 2009 at 12:45 am

    #944192

    [font="Verdana"]You have to use Dynamic SQL in SProc, shown below

    Create Proc dbo.usp_Update

    (

    @table VarChar(25)

    ,@param1 VarChar(25)

    ,@param2 VarChar(25)

    )

    As

    Begin

    Begin Tran

    Declare @strSQL VarChar(MAX)

    Set @strSQL = 'Update ' + @table + ' Set Col1 = ' + @param1

    + ' And Col2 = ' + @param2

    -- Print @strSQL

    sp_ExecuteSQL(@strSQL)

    If @@Error = 0

    Begin

    Commit Tran

    End

    Else

    Begin

    Rollback Tran

    End

    End

    Go

    One more thing, first print the variable and parse to confirm whether the build statement is syntactically proper.

    Mahesh[/font]

    MH-09-AM-8694

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply