Wellspring - 1 year ago 48
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 *
WHERE ADDR.Status = 'A'
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'.

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

DECLARE @InState bit
SET @InState = 1

SELECT *
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