CTE inside SQL IF-ELSE structure

common-table-expression, sql-server

Solution

If possible, find a way to avoid the `if` statement entirely.

E.g. in such a trivial example as in your question:

;with CTE as (
      select UserEmail from UserTable where @a = 1
      union all
      select UserID from UserTable where @a != 1 or @a is null
)
select /* single select statement here */

It should generally be possible to compose one or more distinct queries into a final `UNION ALL` cte, instead of using `if` - after all, both of the queries being combined must have compatible result sets anyway, for your original question to make sense.

Problem

I want to do something like this ``` declare @a int=1 if (@a=1) with cte as ( select UserEmail from UserTable ) else with cte as ( select UserID from UserTable ) select * from cte ``` This is just the example, my actual query is far more complex. So I don't want to write the `SELECT` statement inside `IF` and `ELSE` statement twice after the CTE.

Original source