title: Creating stored procedure for updating columns by case statement
body: <p>I know there are quite a few questions about this but I couldn't find an answer to mine. </p>
  
  <p>I have a table that looks like this:</p>
  
  <pre><code>+----+--------+--------+----------+
  | ID | FormID | ItemID | StrataID |  
  +----+--------+--------+----------+
  | a  | b      |   1111 | NULL     |  
  | a  | b      |   2222 | NULL     |  
  | a  | b      |   3333 | NULL     |  
  | a  | g      |   4563 | NULL     |  
  | b  | f      |   6666 | NULL     |  
  +----+--------+--------+----------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> 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'
  </code></pre>
  
  <p>So the final result is:</p>
  
  <pre><code>+----+--------+--------+----------+--+
  | ID | FormID | ItemID | StrataID |  |
  +----+--------+--------+----------+--+
  | a  | b      |   1111 | 1        |  |
  | a  | b      |   2222 | 2        |  |
  | a  | b      |   3333 | 5        |  |
  | a  | g      |   4563 | NULL     |  |
  | b  | f      |   6666 | NULL     |  |
  +----+--------+--------+-------
  </code></pre>
  
  <p>But i would like to avoid duplicating the case statement for every FormID because I have over 2000 records to update in this manner.</p>
  
  <p>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: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>When I try to execute it I get an error - </p>
  
  <pre><code>Msg 137, Level 15, State 2, Line 18
  Must declare the scalar variable "@NewValue".
  </code></pre>
  
  <p>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.  </p>
  
  <p>Any advice on how to convert my sql to a dynamic update sql? 
  Thanks in advance!!</p>