Using CTE Common table expressions with SELECT CASE statement

Question

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

Answer 1

0

What you're describing will actually change the shape of the resultset.

You could easily do it in a stored procedure using an IF statement. Otherwise, assuming your CTEs have the same columns, you could do something like:

WITH cte1 as ..., cte2 as ...
SELECT * FROM cte1
WHERE <condition>
UNION ALL
SELECT * FROM cte2
WHERE <not condition>
;

answered Feb 27 '10 at 06:46 AM

Rob Farley
5.7k ● 13 ● 17 ● 19

The SQL query in both cases [IF and ELSE] returns the same number of columns and data types. I wanted to know if CTE's are accessible from If and ELSE locations. From BoL, CTE's must be followed by a SELECT, INSERT, etc.. statements only. So I re-wrote it using SELECT CASE statement. Where CASE 1 runs the first CTE CASE 2 runs the second CTE. But that is giving me error too.

Feb 27 '10 at 01:48 PM UB

You can't say ‘case when ... then (select ...)‘ with something that returns multiple columns. CASE must return a single value only.

Feb 27 '10 at 07:36 PM Rob Farley

Makes sense. I did not think of that. Thanks. I got around the problem in a different way thought. Thanks for your help.

Feb 28 '10 at 01:09 AM UB

(comments are locked)

Answer 2

0

Is there a reason you cannot use multiple statements like so:

Declare @R int
Set @R = 1

If @R = 1
    With Sam As 
        (
        Select ...
        From Test
        )
    Select ...
    From Sam

Else
    Select 200

answered Feb 27 '10 at 10:43 PM

Thomas 1
51 ● 1

(comments are locked)

Answer 3

The CASE expression has two formats:

Simple CASE Expression: compares an expression to a set of simple expressions to determine the result. Syntax CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Searched CASE Expression: evaluates a set of Boolean expressions to determine the result.

Syntax CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

a good referance with example:http://cybarlab.blogspot.com/2013/02/sql-case-statementexpression.html

Hope it will help you.

Thanks n regard

1: http://cybarlab.blogspot.com/2013/02/sql-case-statementexpression.html, The CASE expression has two formats: