Skip to content
Advertisement

Execute Query Inside Column From CTE

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:

enter image description here

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)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement