Using CTE Common table expressions with SELECT CASE statement

Question

question

UB asked Feb 27, '10

Using CTE Common table expressions with SELECT CASE statement

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

sql-server-2005 sql cte common-table-expression

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-27T06:46:31Z

Rob Farley answered Feb 27, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-02-27T22:43:25Z

Thomas 1 answered Feb 27, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2013-03-27T07:00:31Z

cybarcom answered Mar 27, '13

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][1] 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: 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][1] Hope it will help you. Thanks n regard [1]: http://// cybarlab.blogspot.com/2013/02/sql-case-statementexpression.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question