I have a CTE table which returns dynamic SQL.
Here is my CTE query:
DECLARE @Qry VARCHAR(MAX) ;WITH CTE AS ( SELECT Qry = 'UPDATE data_'+ REPLACE(mrp.father_uid, '-', '_')+' SET done = 2 WHERE uid = '''+ CAST(mrp.uid AS VARCHAR(250)) + '''' FROM [4928_MyProcessDB].dbo.main_result_process mrp WITH (NOLOCK) INNER JOIN [4928_MyProcessDB].dbo.main_result_process_part mrpp WITH (NOLOCK) ON mrp.uid = mrpp.process_uid ) SELECT * FROM CTE
which returns:
I want to execute each row on the CTE table. How can I do that?
Advertisement
Answer
With just a couple of tweaks
DECLARE @Qry VARCHAR(MAX) = '' ;WITH CTE AS ( SELECT Qry = ';UPDATE data_'+ REPLACE(mrp.father_uid, '-', '_')+' SET done = 2 WHERE uid = '''+ CAST(mrp.uid AS VARCHAR(250)) + '''' FROM [4928_MyProcessDB].dbo.main_result_process mrp WITH (NOLOCK) INNER JOIN [4928_MyProcessDB].dbo.main_result_process_part mrpp WITH (NOLOCK) ON mrp.uid = mrpp.process_uid ) SELECT @Qry = @Qry + Qry FROM CTE Exec(@Qry)