CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel Congrove
Daniel Congrove

Reputation: 3669

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

Upvotes: 1

Views: 589

Answers (4)

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

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

Upvotes: 4

Biju jose
Biju jose

Reputation: 273

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.

Upvotes: 1

user1111
user1111

Reputation: 2020

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

Upvotes: 1

Related Questions