Home Creating stored procedure for updating columns by case statement
 I know there are quite a few questions about this but I couldn't find an answer to mine. I have a table that looks like this: +----+--------+--------+----------+ | ID | FormID | ItemID | StrataID | +----+--------+--------+----------+ | a | b | 1111 | NULL | | a | b | 2222 | NULL | | a | b | 3333 | NULL | | a | g | 4563 | NULL | | b | f | 6666 | NULL | +----+--------+--------+----------+  I would like to update the values for StrataID based on their ID+FormID+ItemID. The values i would like to insert are not stored in any table in the data base they are just a separated list that I have. if i'm using a regular case statement to update the table it looks like this:  UPDATE [Table1] SET [StrataID] = CASE ([ItemID]) when 111 then 1 when 222 then 2 when 333 then 5 else [StrataID] END WHERE [ID] = 'a'and [FormID] = 'b'  So the final result is: +----+--------+--------+----------+--+ | ID | FormID | ItemID | StrataID | | +----+--------+--------+----------+--+ | a | b | 1111 | 1 | | | a | b | 2222 | 2 | | | a | b | 3333 | 5 | | | a | g | 4563 | NULL | | | b | f | 6666 | NULL | | +----+--------+--------+-------  But i would like to avoid duplicating the case statement for every FormID because I have over 2000 records to update in this manner. I tried the following dynamic sql, where @ColumnName are all the available FormID's for a specific ID+FormID and @NewValue is the string of values I would like to insert to the 'StrataID' field instead of the NULLS: DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) DECLARE @NewValue AS NVARCHAR(MAX) SELECT @ColumnName = STUFF((SELECT distinct ',' + QUOTENAME(ItemID) FROM [Table1] where [ID] = 'a'and [FormID] = 'b' FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, ''); SET @NewValue = ',1,2,5,'; SET @DynamicPivotQuery = 'UPDATE [Table1] SET [StrataID] = '+@NewValue+' WHERE[ID] = ''a'' and [FormID] = ''b'' AND ItemID ='+@ColumnName+';' --Execute the Dynamic Query EXEC sp_executesql @DynamicPivotQuery  When I try to execute it I get an error - Msg 137, Level 15, State 2, Line 18 Must declare the scalar variable "@NewValue".  I understand that it might be treating @NewValue as as a string and not integers but I am not sure how to store the values that I need to set as the new values differently and how to create a dynamic sql statement from duplicated case statements. Any advice on how to convert my sql to a dynamic update sql? Thanks in advance!!