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

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#
Sunil Reply to 2018-01-11 06:25:56Z

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.

  1. In this approach, you collect all your ItemId values in table variable @ItemValues and the corresponding StrataId values in a table varaible @StrataIDValues.
  2. 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;
    
niks
3#
niks Reply to 2018-01-11 04:36:54Z

by looking your query the first thing I noticed is that missing single quote for update column value and ItemID should be IN instead of = because you have more than one column name. It should be "SET [StrataID] = ''+@NewValue+'' WHERE[ID] = ''a'' and [FormID] = ''b'' [SectionID] = 1 AND ItemID IN '+@ColumnName+';'

Print @DynamicPivotQuery and excute to see what is the actual error. send the actual update statement and error as well to find the issue.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO