Generate multiple UPDATE statements and execute them

Question

I need to generate multi-update statement.

This code:

DECLARE @query as varchar(max);
SET @query =

'declare @data_dzis as nvarchar(10)
declare @data_wczoraj as nvarchar(10)
SELECT @data_dzis=convert(varchar,convert(int,convert(datetime,convert(date,GETDATE()))))
SELECT @data_wczoraj=convert(varchar,convert(int,convert(datetime,convert(date,GETDATE()-1))))

    Select
        ''update V_''+IdStr + '' set start='' + @data_wczoraj + '' where DataVersionId=0 and start='' + @data_dzis +'';''
    FROM [KW_GRECOS].[dbo].[Variable]
    where Granularity=0 and IdStr<>''_ROOT_FOLDER_'''

exec(@query);

...generates list of updates as a recordset:

update V_G012 set start=43085 where DataVersionId=0 and start=43086;
update V_G059 set start=43085 where DataVersionId=0 and start=43086;
update V_G002a set start=43085 where DataVersionId=0 and start=43086;
update V_G0122 set start=43085 where DataVersionId=0 and start=43086;
update V_103D set start=43085 where DataVersionId=0 and start=43086;
update V_G072 set start=43085 where DataVersionId=0 and start=43086;
update V_G201 set start=43085 where DataVersionId=0 and start=43086;
update V_G001a set start=43085 where DataVersionId=0 and start=43086;
update V_G067a set start=43085 where DataVersionId=0 and start=43086;

I don't know how to execute them automatically. I had to copy them and manually and execute, but that's not my point.

Because it boils down to needing a tutorial on dynamic SQL, this might as well be a duplicate of [Dynamic SQL - EXEC(@SQL) versus EXEC SP\_EXECUTESQL(@SQL)](https://stackoverflow.com/questions/548090/dynamic-sql-execsql-versus-exec-sp-executesqlsql) — underscore_d, Dec 19 '17 at 14:58

score 2 · Accepted Answer · answered Dec 19 '17 at 16:12

First, Your update statements make it seem like you have different tables describing the same data entities. That is a bad database design and should be refactored if possible.
All these V_ + IdStr tables should be a single table, where the IdStr is just another column inside.

Assuming this can't be done, I believe the rest of my answer is what you are looking for:

Change your @query variable to nvarchar(max) instead of varchar(max) and use sp_executeSql to get the update statements as an output variable.
Add a variable to concatenate the query results into (I've called it @statements in my example, note the lines with the Added this! comment):

DECLARE @query as nvarchar(max),
        @Update nvarchar(max);

SET @query =

'declare @data_dzis as nvarchar(10)
declare @data_wczoraj as nvarchar(10)
SELECT @data_dzis=convert(varchar,convert(int,convert(datetime,convert(date,GETDATE()))))
SELECT @data_wczoraj=convert(varchar,convert(int,convert(datetime,convert(date,GETDATE()-1))))
Set @statemets = '''';  -- Added this!
Select @statemets += -- Added this!
        ''update V_''+IdStr + '' set start='' + @data_wczoraj + '' where DataVersionId=0 and start='' + @data_dzis +'';''
    FROM [KW_GRECOS].[dbo].[Variable]
    where Granularity=0 and IdStr<>''_ROOT_FOLDER_'''

EXECUTE sp_executesql @query, N'@statemets nvarchar(max) OUTPUT', @statemets=@Update OUTPUT

PRINT @Update
--EXECUTE sp_executesql  @Update

Once you see that the @Update variable contains the correct update statements, remove the Print row and uncomment the Execute row.

See a simplified live demo on rextester.

paparazzo · Answer 2 · 2017-12-19T21:23:48.570

You don't need to do all that in the @query
Not tested

declare @data_dzis    as nvarchar(10)
declare @data_wczoraj as nvarchar(10)
SELECT @data_dzis    = convert(varchar,convert(int,convert(datetime,convert(date,GETDATE()))))
SELECT @data_wczoraj = convert(varchar,convert(int,convert(datetime,convert(date,GETDATE()-1))))

DECLARE @query as nvarchar(max);
SET @query = 'Select ''update V_''+ IdStr + '' set start='' + @data_wczoraj + 
             '' where DataVersionId=0 and start='' + @data_dzis +'';''
             FROM [KW_GRECOS].[dbo].[Variable]
             where Granularity=0 and IdStr<>''_ROOT_FOLDER_'''

exec(@query);

While your observation is correct, it doesn't answer the question. Also, you forgot a `'` before the `Select`. — Zohar Peled, Dec 19 '17 at 16:32

Generate multiple UPDATE statements and execute them

2 Answers2