How to use common table expression in a dynamic SQL

Question

I have to write a dynamic pivot based on a complex query and I want to use a common table expression to create the dataset on which I have to build the pivot to keep it outside the dynamic sql and have it compiled

My problem is that I don't know if i can use the CTE in a SET where I wrap the dynamic SQL I have to execute.

let see the code:

WITH DatiCTE AS
    (
    SELECT ...
    )

    SET @DynamicPivotQuery = 
          N'SELECT IdActivity, ' + @PivotSelectColumnNames + '
    FROM DatiCTE
    PIVOT(SUM(NumOfDays) 
          FOR Area IN (' + @PivotColumnNames + ')) AS PVTTable'
          WHERE 1 = 1

EXEC sp_executesql @DynamicPivotQuery

This way i get an error near SET @DynamicPivotQuery =

If I replace SET with a SELECT the stored procedure is compiled but if i run it I get:

Invalid object name 'DatiCTE'

Mahmoud Gamal · Accepted Answer · 2015-10-13 10:24:47Z

4

Move the definition of the CTE WITH DatiCTE AS to be inside the dynamic sql like this:

SET @DynamicPivotQuery = 
  N'WITH DatiCTE AS
    (
       SELECT ...
    )
    SELECT IdActivity, ' + @PivotSelectColumnNames + '
    FROM DatiCTE
    PIVOT(SUM(NumOfDays) 
      FOR Area IN (' + @PivotColumnNames + ')) AS PVTTable'
      + 'WHERE 1 = 1';

EXEC sp_executesql @DynamicPivotQuery;

answered Oct 13, 2015 at 10:24

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gt.guybrush Over a year ago

the scope of the CTE is to keep the costant code outside of the dynamic SQL

Muzaffer Galata Over a year ago

Why is this answer upvoted? It does not work.

gt.guybrush · Accepted Answer · 2015-10-13 14:22:31Z

2

for now i switch to temp table:

SELECT ...
INTO @TempTable

SET @DynamicPivotQuery = 
  N'
    SELECT IdActivity, ' + @PivotSelectColumnNames + '
    FROM @TempTable
    PIVOT(SUM(NumOfDays) 
      FOR Area IN (' + @PivotColumnNames + ')) AS PVTTable'
      + 'WHERE 1 = 1';

EXEC sp_executesql @DynamicPivotQuery;

but i have soume doubt about concurrent users. i try table variable but cant be used in dynamic sql...

answered Oct 13, 2015 at 14:22

gt.guybrush

1,4183 gold badges20 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to use common table expression in a dynamic SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related