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?
CodePudding user response:
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)