Case statement in where clause to search for none specific values

March 9, 2022

With Windows SQL would it be possible to set parameters that allows users to select that dictates what results will be returned based on value entered?

Basically, instead of creating 3 separate queries for each where clause option row below, I am hoping when entering a case statement in the where clause the same can be achieved.
For example, if the user selects A, he should retrieve values that are %gre% or %sma%.

declare @value as varchar(2)
set @value = 'A'
select personA, personB 
        from dbo.list 
        where 
        case 
        when @value = 'A' then (option like '%gre%' or option like '%sma%')  
        when @value = 'B' then (option like '%tin%' or option like '%mic%')  
        when @value = 'C' then (option like '%gig%' or option like '%mul%')

If there are any other better ideas, I am open for suggestions.

>Solution :

It looks like you are just trying to do

where
(@value = 'A' and (option like '%gre%' or option like '%sma%')) or
(@value = 'B' and (option like '%tin%' or option like '%mic%')) or
(@value = 'C' and (option like '%gig%' or option like '%mul%')) ;