Declare scalar variable SQL

Question

My problem is i want to use this local variable inside my update query, is there a way of doing this?

 DECLARE @listStr VARCHAR(MAX)
    SELECT @listStr = COALESCE(@listStr+''',''' , '') + cast(int_guid as varchar(max)) 
    FROM ex_in
    SELECT '''' + @listStr + ''''

Update query

update dbo.ex_in
SET    int_action = CASE WHEN int_action = 120 THEN 110 WHEN int_action = 220 THEN 210 ELSE int_action END
WHERE  int_action IN (120,220)
       AND int_guid in(@listStr)

Like [Gordon](http://stackoverflow.com/a/28835333/2246380) has used in his answer `in (SELECT int_guid FROM ex_in)`. Learn more [here](https://technet.microsoft.com/en-us/library/ms189575%28v=sql.105%29.aspx) — Ram, Mar 03 '15 at 15:30

score 0 · Answer 1 · answered Mar 03 '15 at 15:14

0

No, because in on a string variable does not do what you expect.

But, why bother with the variable? Just do:

update dbo.ex_in
    SET int_action = (CASE WHEN int_action = 120 THEN 110 WHEN int_action = 220 THEN 210 ELSE int_action END)
    WHERE int_action IN (120, 220) AND
          int_guid in (SELECT int_guid FROM ex_in);

Of course, now that I write this, the in logic is unnecessary, because there both queries are on the same table and there is no filtering. Just use:

update dbo.ex_in
    SET int_action = (CASE WHEN int_action = 120 THEN 110 WHEN int_action = 220 THEN 210 ELSE int_action END)
    WHERE int_action IN (120, 220);

answered Mar 03 '15 at 15:14

Gordon Linoff

1,122,135
50
484
624

So there is no way i can use that variable @listStr for int_guid in? The list is gonna change very fast so its important it wont change on just int_action – Girre Mar 03 '15 at 15:20
The only way to use @listStr is to build a string with dynamic SQL and then execute that entire string... – pmbAustin Mar 03 '15 at 16:59

score 0 · Accepted Answer · answered Mar 03 '15 at 17:18

The only way to do what you want that I can think of is using Dynamic SQL, like so:

DECLARE @sql varchar(MAX);

SET @sql = '
UPDATE dbo.ex_in
SET    int_action = CASE WHEN int_action = 120 THEN 110 
                         WHEN int_action = 220 THEN 210 
                         ELSE int_action 
                    END;
WHERE  int_action IN (120,220)
       AND int_guid IN (' + @listStr + ')
';

EXEC @sql;

Declare scalar variable SQL

2 Answers2