Home Creating stored procedure for updating columns by case statement
Reply: 0

Creating stored procedure for updating columns by case statement

user2970
1#
user2970 Published in July 18, 2018, 10:30 pm

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!!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.311073 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO