Sql Server union with If condition

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

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 .