switch case in where clause

Question

hi,

i need to check three conditions:

if @filter = 1
{
    select * from employeestable where rating is not null
}
else if @filter = 2
{
    select * from employeestable where rating is null
}
else
{
    select * from employeestable  
}

This i need to do using a case statement. now i have more than 30 lines of query, if i use case i can reduce my code upto 70%
Please let mek now how can i do this.

Answer 1

How about this?

WHERE   (@filter = 1 AND rating IS NOT NULL)
OR      (@filter = 2 AND rating IS NULL)
OR      (@filter <> 1 AND @filter <> 2)

Answer 2

You could take advantage of bit logic.

Test data

DECLARE @employeestable TABLE (rating INTEGER)
INSERT INTO @employeestable
SELECT NULL
UNION ALL SELECT 99

DECLARE @filter INTEGER
SET @filter = 3

SQL Statement

SELECT  *
FROM    (
          SELECT  Filter = CASE WHEN rating IS NULL THEN 2 ELSE 1 END
                  , *
          FROM    @employeestable
        ) et
WHERE   et.Filter & @filter = et.filter

Answer 3

reducing your code by 70% is nice, but using an index is the only way to make the query perform well. Read this article: Dynamic Search Conditions in T-SQL by Erland Sommarskog,. There are numerous ways to have dynamic search conditions, each with their own subtle performance trade offs. If you are worried about repeating the bulk of the query multiple times, then consider replacing it with a view.