question

UB avatar image
UB asked

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-2005sqlctecommon-table-expression
10 |1200

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

Rob Farley avatar image
Rob Farley answered

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

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

Thomas 1 avatar image
Thomas 1 answered

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

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

cybarcom avatar image
cybarcom answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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