Using Logic in TSQL

Question

I want to do something like this in TSQL (SQL Server 2005):

IF (Column1 = x)
{
     --CTE statement
}
ELSE
{
    --SQL statement
}

Any help is appreciated.

Answer 1

Is that part of a query? or on its own?

Outside of SELECT, you have:

IF ([test])
BEGIN
     [true branch]
END
ELSE
BEGIN
    [false branch]
END

The branches can do anything, including use CTEs etc.

Inside a query, you have CASE:

SELECT ..., CASE WHEN Column1=x THEN [answer1]
                 ELSE [answer2] END, ...

However, you can't do a CTE inside CASE

Answer 2

The CTE applies to the whole statement and is not standalone. It's a clause or sub-construct in a larger SQL construct.

This works if the output from each statement is different

IF EXISTS (SELECT * FROM table WHERE Column1 = x)
BEGIN
    ;WITH cStuff AS
    (
        ...
    )
    SELECT 
        ...
    FROM
        tables and cStuff
END
ELSE
BEGIN
    SELECT 
        ...
    FROM
        tables
END

This works if the output is the same:

;WITH cStuff AS
(
    ...
)
SELECT 
    ...
FROM
    tables and cStuff
WHERE
    column1 = x
UNION ALL
SELECT 
    ...
FROM
    tables
WHERE
    column1 <> x

Otherwise, I'm unsure what you want to achieve...