Wellspring Wellspring - 5 months ago 10
SQL Question

TSQL Equal or Not Equal conditional in Where clause

Based on a declared variable, is it possible to switch between not equal and equal in the where clause?

For example, if the variable @InState is set to true, then return all the addresses with a state equal to 'CA', if false, return all of the addresses with states <> 'CA'.

Code:

DECLARE @InState bit
SET @InState = 1

SELECT *
FROM dbo.tAddresses ADDR
WHERE ADDR.Status = 'A'
AND ADDR.State =
CASE WHEN @InState = 1
THEN
'CA'
ELSE
''


The ELSE in the code above, returns every state including 'CA'. I need to return every other state, not equal to 'CA'.

Answer

I'd try to avoid CASE statements in the WHERE clause and go for something like this;

DECLARE @InState bit
SET @InState = 1

SELECT * 
FROM dbo.tAddresses ADDR
WHERE ADDR.Status = 'A'
AND 
(
    (@InState = 1
    AND ADDR.State = 'CA')
    OR
    (@InState <> 1
    AND ADDR.State <> 'CA')
)

Calculations in a WHERE clause is going to kill your performance by making your query non SARGable;

https://en.wikipedia.org/wiki/Sargable