CODEWITHSUNDEEP
sql-servert-sql
Roger
Roger

Reputation: 1631

Dynamic SQL in a Common Table Expression in TSQL

I need to return a dynamic SQL using a CTE (Common Table Expression) in TSQL, my attempt below doesn't work:

NOTE: the query below uses a linked server, but my question is related to any SQL even not using the openquery command.

declare @sql nvarchar(max) = N'SELECT * FROM OPENQUERY(DATABASE1, ''SELECT * FROM tblRequisitions'')'

;WITH requisitions AS
(
   EXEC (@sql)
)
SELECT * from requisitions

How can I make this dynamic SQL to load into my CTE?

Upvotes: 2

Views: 1799

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

You can't incorporate dynamic sql into regular sql like that. What you can do is create a temp table, insert the dynamic output into the temp table and then reference the temp table in the CTE. Something along these lines...

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;

CREATE TABLE #temp (
    -- ColumnName DataType
    );
INSERT #temp (ColumnName)
EXEC (@sql);

WITH
    cte_requisitions AS (
        SELECT 
            *
        FROM 
            #temp t
        )
SELECT 
    *
FROM 
    cte_requisitions r;

Upvotes: 4

Related Questions