Update statement in a parameterized stored procedure

• pmathema1

  SSC Enthusiast

  Points: 181

  More actions

  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

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  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