Home > database >  Execute Query Inside Column From CTE
Execute Query Inside Column From CTE

Time:09-30

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?

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)
  • Related