Sql Server union with If condition
50,212
Solution 1
Move the condition @tmpValue > 0
to the WHERE
clause like so:
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal WHERE @tmpValue > 0 AND Active = 0
Solution 2
Best way to put condition in Query is CASE statement . you can put any number of condition in query . CASE statement is used to put conditional filters in Query .
For EX.
DECLARE @tmpValue
SET @tmpValue = 0 -- it will be change
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal
WHERE
1 = CASE WHEN @tmpValue = 0 THEN 0 ELSE Active = 1 END
your situation is not to complex but for more complex condition you can use nested CASE statement in Query .
Solution 3
You can add your condition to the query like this. the section part of the union will simply return no results if your test condition is false:
DECLARE @tmpValue
SET @tmpValue = 0 -- it will be change
SELECT * FROM Animal WHERE AniActive = 1
UNION
SELECT * FROM Animal WHERE.Active = 0 and @tmpValue > 0
Author by
John
Updated on July 05, 2022Comments
-
John about 2 years
I have a query like:
DECLARE @tmpValue SET @tmpValue = 0 -- it will be change SELECT * FROM Animal WHERE AniActive = 1 UNION IF @tmpValue > 0 SELECT * FROM Animal WHERE.Active = 0
When I use like this it is giving error because of if condition. I have to use UNION because of our structure.
How can I use it with if condition?
Thanks,
John