How to use Case statement in Where clause in SQL Server

Assume the table is like this

SNO	SNAME	GRADE	PASS
1	Kesav	1	1
2	Kishore	1	1
3	Srinath	2	1
4	Srinu	3	1
5	Sunil	1	0
6	Swapna	2	1
7	Venuka	0	1
8	Suneetha	0	1
9	Venky	0	1
10	Kamala	0	1

@grade input takes above_0/equallto_0/allpassed

Create proc normal_proc(@grade nvarchar(10))
as
begin
   if(@grade='above_0')
      select * from tab where grade in (1,2,3) and pass=1
   else if(@grade='equallto_0')
      select * from tab where grade in (0) and pass=1
   else
      select * from tab where pass=1
end

Now usage of case statement in where clause like follows....

create proc Case_in_Where(@grade nvarchar(10))
as
begin
   select * from tab
      where ((grade=case when @grade='above_0' then 1
         when @grade='equallto_0' then 0
         else grade end)
      or (grade=case when @grade='above_0' then 2
         when @grade='equallto_0' then 0
         else grade end)
      or (grade=case when @grade='above_0' then 3
         when @grade='equallto_0' then 0
         else grade end))
      and pass=1
end

You can follow either of the procedures above mentioned but comming to huge number of comparisions in where condition "Case_in_Where" is the best option


exec check_proc 'above_0'

SNO	SNAME	GRADE	PASS
1	Kesav	1	1
2	Kishore	1	1
3	Srinath	2	1
4	Srinu	3	1
6	Swapna	2	1

(5 row(s) affected)


exec check_proc 'equallto_0'

SNO	SNAME	GRADE	PASS
7	Venuka	0	1
8	Suneetha	0	1
9	Venky	0	1
10	Kamala	0	1

(4 row(s) affected)


exec check_proc 'allpassed'

SNO	SNAME	GRADE	PASS
1	Kesav	1	1
2	Kishore	1	1
3	Srinath	2	1
4	Srinu	3	1
6	Swapna	2	1
7	Venuka	0	1
8	Suneetha	0	1
9	Venky	0	1
10	Kamala	0	1

(9 row(s) affected)


Tag: case in sql server, case in sql server 2005, Case with Where clause in SQL Server, using case in sql server, using case in sql server where clause