Home Creating stored procedure for updating columns by case statement

# Creating stored procedure for updating columns by case statement

A.Lot
1#
A.Lot Published in 2018-01-11 00:24:06Z
 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!!
Sunil
2#
 You need to make sure that everything is nvarchar type when concatenating parts to form your dynamic query else you would get errors. Therefore, you should be using the following code rather than what you have right now. Note that all parameter values are being cast to nvarchar data type in code below. SET @DynamicPivotQuery = 'UPDATE [Table1] SET [StrataID] = '+ cast(@NewValue as nvarchar(100)) +' WHERE[ID] = ''a'' and [FormID] = ''b'' AND ItemID ='+ cast( @ColumnName as nvarchar(500)) +';'  However, you don't need a dynamic query to tackle your scenario. Instead follow the approach below. In this approach, you collect all your ItemId values in table variable @ItemValues and the corresponding StrataId values in a table varaible @StrataIDValues. Make sure that the order of values in both tables are same, so that first record in @ItemValues table corresponds to first record in @StrataIdValues table. Update without dynamic query DECLARE @ItemValues TABLE ( SequenceNumber int IDENTITY (1, 1), ItemID int ) INSERT INTO @ItemValues (ItemID) SELECT ItemId FROM Table1 WHERE [ID] = 'a' AND [FormID] = 'b'; --populate the corresponding values for StrataID in same sequence as --ItemId values in above table DECLARE @StrataIDValues TABLE ( SequenceNumber int IDENTITY (1, 1), StrataID int ) INSERT INTO @StrataIDValues (StrataID) VALUES (1), (2), (5); UPDATE [Table1] SET [StrataID] = v.StrataID FROM Table1 t INNER JOIN (SELECT ItemID, StrataID FROM @ItemValues INNER JOIN @StrataIDValues ON [@ItemValues].SequenceNumber = [@StrataIDValues].SequenceNumber) v ON t.ItemID = v.ItemID;