TSQL Equal or Not Equal conditional in Where clause

Question

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'.

Possible duplicate of [Conditional where clause in Sql Server?](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, Jun 15 '16 at 14:44

Rich Benner · Accepted Answer · 2016-06-15T15:34:39.770

4

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

Test Data;

DECLARE @Instate bit; SET @Instate = 1

CREATE TABLE #Addresses (ID int, State varchar(2), Status varchar(2))
INSERT INTO #Addresses (ID, State, Status)
VALUES
(1,'CA','A')
,(2,'NY','A')
,(3,'CA','A')
,(4,'NJ','A')
,(5,'FL','A')
,(6,'CA','A')
,(7,'ND','A')

Query

DECLARE @InState bit
SET @InState = 1

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

When @Instate = 1 then these are the results;

ID  State   Status
1   CA      A
3   CA      A
6   CA      A

When @Instate = 0 then you get this;

ID  State   Status
2   NY      A
4   NJ      A
5   FL      A
7   ND      A

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

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

edited Jun 15 '16 at 15:34

answered Jun 15 '16 at 14:28

Rich Benner

7,873
9
33
39

Thanks Rich, this worked for me. I did have to modify it slightly though to get it to display the out of state addresses. Changed the line `@InState <> 1` to `(@InState <> 1 AND ADDR.State <> 'CA')` – Daniel Congrove Jun 15 '16 at 14:36
Yeah, I noticed that, i think you'll need to change that to a <> rather than = take a look at my updated answer. – Rich Benner Jun 15 '16 at 14:37
Yeah, thanks. That was a typo in my comment. Fixed it. – Daniel Congrove Jun 15 '16 at 14:37
Thanks again! Sorry for the delay, had to wait on the stack overflow timer to mark a question as accepted. – Daniel Congrove Jun 15 '16 at 14:39
1

No worries at all, as long as your problem is sorted then I'm happy. Cheers – Rich Benner Jun 15 '16 at 14:39

score 1 · Answer 2 · answered Jun 15 '16 at 14:38

1

This is the ugly one, but it works for me,

....

AND ADDR.State =
CASE WHEN @InState = 1 THEN 'CA' ElSE ADDR.State END
AND ADDR.State <>
CASE WHEN @InState = 0 THEN 'CA' ElSE ADDR.State END

answered Jun 15 '16 at 14:38

user1111

1,840
2
14
18

I think this would suppress all results, don't you want to wrap those last two in brackets and use an OR between them? – Rich Benner Jun 15 '16 at 14:59
You are right. I used '=' to test both cases. However, it does have issues when it's NOT equal. This should be OK ... CASE WHEN @InState = 0 THEN 'CA' ElSE 'something non-exist' END – user1111 Jun 15 '16 at 15:18

paparazzo · Answer 3 · 2016-06-15T16:07:54.880

1

Not a where clause but I think it is sargable

SELECT ADDR.* 
FROM dbo.tAddresses ADDR
left join dbo.tAddresses ADDRin 
      on ADDRin.id = ADDR.id  
     and ADDRin.State = 'CA' 
     and @InState = 1
left join dbo.tAddresses ADDRout 
      on ADDRout.id = ADDR.id 
     and ADDRout.State <> 'CA' 
     and @InState = 0
WHERE ADDR.Status = 'A'
  AND (ADDRin.id is not null or ADDRout.id is not null)


SELECT ADDR.* 
  FROM dbo.tAddresses ADDR
 where ADDR.Status = 'A' 
   and ADDR.State  = 'CA' 
   and @InState = 1
UNION ALL
SELECT ADDR.* 
  FROM dbo.tAddresses ADDR
 where ADDR.Status = 'A'
   and ADDR.State <> 'CA' 
   and @InState <> 1

edited Jun 15 '16 at 16:07

answered Jun 15 '16 at 14:49

paparazzo

44,497
23
105
176

That'll just return everything in ADDR though surely? By doing both of those inner joins you're not actually excluding anything. – Rich Benner Jun 15 '16 at 14:58
@RichBenner Surely? Why don't you test it out. – paparazzo Jun 15 '16 at 15:03
I've just tested it and it suppresses all results, regardless of whether @instate is set to 1 or 0 – Rich Benner Jun 15 '16 at 15:08
@RichBenner So first you assert it will return everything and then you test and returns nothing. And you down vote. Not compelled to educate you. – paparazzo Jun 15 '16 at 15:16
Just a note, I haven't downvoted. Just trying to make sure that the OP gets the correct answer. I've put a picture on my answer to prove I haven't downvoted. – Rich Benner Jun 15 '16 at 15:19
If your answer had left joins and this code at the end it would work perfectly 'AND (ADDRin.ID IS NOT NULL OR ADDRout.ID IS NOT NULL)' – Rich Benner Jun 15 '16 at 15:25

score 1 · Answer 4 · answered Jun 15 '16 at 15:00

1

How about using IF ELSE

IF (@Instate=1)
BEGIN 
SELECT * 
FROM dbo.tAddresses ADDR
WHERE ADDR.Status = 'A'
AND ADDR.State ='CA'
END
ELSE 
BEGIN
SELECT * 
FROM dbo.tAddresses ADDR
WHERE ADDR.Status = 'A'
AND ADDR.State <>'CA'
END

I know its procedural not sure about the performance aspect. But another option.

answered Jun 15 '16 at 15:00

Biju jose

263
2
15

1

Thank Biju for sharing that solution. I'm still learning more about SQL, so it's always good to see more than one way to solve the same problem. – Daniel Congrove Jun 15 '16 at 15:15

TSQL Equal or Not Equal conditional in Where clause

4 Answers4