Hi, This gives me error.
DECLARE @r INT
SELECT @r = 1
;WITH sam AS (select * from test)
SELECT 1, CASE @r
WHEN 1 THEN (SELECT * FROM sam)
WHEN 2 THEN 200
ELSE 0
END
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
What I need is a way to run different CTE based on the condition. Something like this:
WITH CTE_1 AS (SELECT * FROM test1)
, CTE_2 AS (SELECT * FROM test2)
IF <condition A>
SELECT FROM CTE_1
ELSE
SELECT * FROM CTE_2
But since, CTE's must be followed by a SELECT, INSERT., etc statements only, I tried to use SELECT CASE statement, s given above.
How do I do this, other than defining CTE's twice.
thanks, _Ub