Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Union All With if/else T-SQL

Tags:

sql

tsql

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.

like image 325
LB. Avatar asked Dec 02 '22 04:12

LB.


2 Answers

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 ;
like image 132
nvogel Avatar answered Dec 03 '22 23:12

nvogel


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)
like image 41
Roopesh Shenoy Avatar answered Dec 04 '22 00:12

Roopesh Shenoy