In case the offer
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 must be done using a case statement. now i have over 30 lines of query, if i use case i can reduce my code to 70%
Please let me now how can i do this.
+2
a source to share
3 answers
reducing your code by 70% is nice, but using an index is the only way to make the query run. Read this article: Dynamic search terms in T-SQL from Sommarskog of Erland, . There are many ways to have dynamic search terms, each with its own subtle tradeoffs. If you are worried about repeating the main body of the query multiple times, consider replacing it with a view.
+2
a source to share
You can use 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
0
a source to share