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.
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 ;
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With