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)

Answer 1

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;