CODEWITHSUNDEEP
sqlwhere-clause
MisterIsaak
MisterIsaak

Reputation: 3922

Better way to special case where clause?

I have a "special case" I need to take into account in a sql script. This is what I have so far and I 'think' it works but it seems messy. Its a sproc so assume there is an @empStatus parameter. If the employee's @empStatus=2 I want to return all the employee's with a Status of 1 or 2.

select *
from Employees
where Title='Associate' and Status=@empStatus and Department='Sales'
    OR (@empStatus=2 and (Status=@empStatus or Status=1) and Title='Associate' and Department='Sales')

Upvotes: 1

Views: 80

Answers (1)

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196177

This should cover it

SELECT 
    *
FROM
    Employees
WHERE
   Title='Associate'
   AND Department='Sales'
   AND ((Status=@empStatus) OR (@empStatus=2 AND Status=1))

Upvotes: 4

Related Questions