CTE inside SQL IF-ELSE structure

Question

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.

Answer 1

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.

Answer 2

You can't do that - the CTE must immediately be followed by exactly one SQL statement that can refer to it. You cannot split the "definition" of the CTE from the statement that uses it.

So you need to do it this way:

declare @a int=1

if (@a=1)
    with cte as 
    (
       select UserEmail from UserTable 
    )
    select * from cte

else

    with cte as
    (
       select UserID from UserTable
    )
    select * from cte

You cannot split the CTE "definition" for its usage (select * from cte)