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'))
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))