I have a CTE table which returns dynamic SQL.
Here is my CTE query:
x
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)