CODEWITHSUNDEEP
sql-server-2005sql-server-2008
nimi
nimi

Reputation: 5507

switch case in where clause

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.

Upvotes: 2

Views: 1010

Answers (3)

KM.
KM.

Reputation: 103667

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.

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 0

David M
David M

Reputation: 72920

How about this?

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

Upvotes: 6

Related Questions