Search code examples
sql-servercasewhere-clauseor-operator

Select case in where clause with OR Operator


I have a Students table which has a column called SEN. SEN contains either an S, A, P or N for every record. In my WHERE clause I would like to filter my results set according to the value in SEN. My SELECT statement contains a variable called subgroup which is populated by parameters. I have managed to get this to work when filtering to individual values is the SEN column ie:

   SEN = CASE
           WHEN @Subgroup = 'SENA' THEN 'A'
           WHEN @Subgroup = 'SENP' THEN 'P'
           WHEN @Subgroup = 'SENS' THEN 'S'
           WHEN @Subgroup = 'SENNo' THEN 'N'
       END 

but I would like to filter where a Student has either A, P or S and trigger this filter using the parameter value 'SENYes'. My code below doesn't work, but may illustrate what I'm asking for.

   SEN = CASE 
           WHEN @Subgroup = 'SENYes' THEN 'A' OR 'P' OR 'S'
           WHEN @Subgroup = 'SENA' THEN 'A'
           WHEN @Subgroup = 'SENP' THEN 'P'
           WHEN @Subgroup = 'SENS' THEN 'S'
           WHEN @Subgroup = 'SENNo' THEN 'N'
       END 

UPDATE:

Using the IN operator in my code worked:

   SEN = CASE 
           WHEN @Subgroup = 'SENA' THEN 'A'
           WHEN @Subgroup = 'SENP' THEN 'P'
           WHEN @Subgroup = 'SENS' THEN 'S'
           WHEN @Subgroup = 'SENNo' THEN 'N'
       END
   OR
   (@SubGroup='SENYes' AND SEN IN ('A','P','S'))

Solution

  • You could use the condition

    Where 
    (@SubGroup='SENYes' and SEN IN ('A','P','S'))
    OR
    (SEN = SubString(@SubGroup,4,1))
    

    for example

    declare @std table (SEN varchar(1))
    Insert into @std
    Select 'A'
    UNION Select 'P'
    UNION Select 'S'
    UNION Select 'N'
    
    declare @Subgroup varchar(10)
    --change commented for testing
    --Select @SubGroup='SENA'
    Select @SubGroup='SENYes'
    
    Select * from @std
    Where 
    (@SubGroup='SENYes' and SEN IN ('A','P','S'))
    OR
    (SEN = SubString(@SubGroup,4,1))