Tricks to use Case-when in where clause

Tricks to useCase-when in where clause

 

1. Problem: 

Given:

DECLARE @PeopleTABLE(        

Namenvarchar(100),        

Gradenvarchar(30))

DECLARE @Levelnvarchar(30)

 

Write a query to

 

If @Level='Excellent',  return all@People.Grade='A'

If @Level='Good',  return all @People.Grade='B'

If @Level='TopTwo',  return all @People.Grade='A'OR 'B'

 

2. Solution:

2.1 Idea1:  compose condition expression in case-when

SELECT * FROM@People

WHERE

CASE@Level

WHEN'Excellent' THEN Grade='A' 

WHEN'Good' THEN Grade='B'

WHEN'TopTwo' THEN Grade IN ('A','B')

ELSE1=0

END

Incorrect syntaxnear '=',  maybe because only valueexpression is supported after 'THEN'

 

2.2 Idea2: prepare valuelist in case-when

SELECT * FROM@People

WHEREGrade IN

(

CASE@Level

WHEN'Excellent' THEN 'A' 

WHEN'Good' THEN 'B'

WHEN'TopTwo' THEN ('A','B')

ELSE'NONE'

END

)

 

Incorrect syntaxnear ',',  not support

 

2.3 Idea3:  use 1 or 0 in the THEN of CASE-WHEN

 

SELECT * FROM People

WHERE1=(

CASE

WHEN@Level='Excellent' AND Grade='A' THEN 1

WHEN@Level='Good' AND Grade='B' THEN 1

WHEN@Level='TopTwo' AND Grade IN ('A','B') THEN 1

ELSE0

END

)