CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expressiondynamic-sql
gt.guybrush
gt.guybrush

Reputation: 1388

How to use common table expression in a dynamic SQL

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'

Upvotes: 2

Views: 17169

Answers (2)

gt.guybrush
gt.guybrush

Reputation: 1388

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...

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

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;

Upvotes: 4

Related Questions