Union All With if/else T-SQL

Question

I was wondering how I can have a UNION ALL with IF/ELSE statement.

For example:

SELECT * FROM A
UNION ALL
SELECT * FROM B
UNION ALL

IF @type = 1
   BEGIN
      SELECT * FROM C
   END
ELSE
   BEGIN
      SELECT * FROM D
   END

UNION ALL

SELECT * FROM E

I get syntax error.

Solution 1

SELECT * FROM A 
UNION ALL 
SELECT * FROM B 
UNION ALL 
SELECT * FROM C WHERE @type = 1
UNION ALL
SELECT * FROM D WHERE @type <> 1 OR @type IS NULL
UNION ALL 
SELECT * FROM E ;

Solution 2

Well one way to do it is use dynamic sql

first build the query string and then execute it. That way you have full control

declare query nvarchar(max)
set query = 'SELECT * FROM A
             UNION ALL
             SELECT * FROM B
             UNION ALL '

IF @type = 1
BEGIN
set query = query + '
            SELECT * FROM C'
END
ELSE
BEGIN
set query = query + '
            SELECT * FROM D'
END
set query = 'UNION ALL
             SELECT * FROM E'

exec(query)