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.