0

I have a Query like below

WITH TempResult AS(
SELECT AccessionNumber FROM [CASE]
UNION ALL
SELECT AccessionNumber FROM CaseReassign), 
TempCount AS (SELECT COUNT(AccessionNumber) AS MaxRows FROM TempResult)
SELECT * FROM TempResult, TempCount

Which works fine fetching the rows and its count. But, when Can someone help me in how to add a filter dynamically to this select query? say for example I need something like below but it throws an error "Common Table Expression defined but not used"

DECLARE @filter nvarchar(max);
SET @filter = 'WHERE AccessionNumber LIKE ''%Case%'''

DECLARE @sql nvarchar(max);

WITH TempResult AS(
SELECT AccessionNumber FROM [CASE]
UNION ALL
SELECT AccessionNumber FROM CaseReassign), 
TempCount AS (SELECT COUNT(AccessionNumber) AS MaxRows FROM TempResult)

SELECT  @sql = 'SELECT * FROM TempResult, TempCount ' + @filter

EXEC sp_executesql @sql
Srini
  • 132
  • 8
  • You could put the whole query with the CTE's in the `@sql` variable, concat the SELECT & filter to it, then execute. A CTE isn't like a #temp table or a table variable. It's only part of the query. – LukStorms Sep 20 '18 at 08:18

1 Answers1

1

Dynamic SQL defines it's own scope. Within dynamic SQL you are not able to access local variables. As far as I know this also counts for local defined CTE's.

So you are required to put your CTE into the dynamic SQL I think.

Josef Biehler
  • 952
  • 4
  • 16