Home > Net >  Case statement in where clause to search for none specific values
Case statement in where clause to search for none specific values

Time:03-10

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.

CodePudding user response:

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%')) ;
  • Related