CODEWITHSUNDEEP
sqlsql-serverdynamicwhere-clause
David.L
David.L

Reputation: 117

Dynamic Where Clause - combination of wildcard, multiple values or 1 value

I created a stored proc with a parameter (@iParam) where user will enter up to a maximum 10 "values". The "values" that user will enter will be a combination and/or ALL of the three types:

  1. Using Wild Card - i.e. ABC%
  2. Multiple Exact Values - i.e. BIRD, CAT, DOG
  3. 1 Exact Value - i.e. WOLF

The question now is, in my WHERE clause, how do I make it dynamic to switch and/or use the LIKE, =, IN operator depending on user's entered "values"? i.e:

  1. Using Wild Card - WHERE clause will need to use LIKE operator
  2. Multiple Exact Values - WHERE clause will need to use IN operator
  3. 1 Exact Value - WHERE clause just need to use = operator

Example 1: If user were to input '%TEXT%' and USA as the values, the WHERE clause needs to know that it'll use LIKE and = operator

Example 2: If user were to input 'STRING%', JOHN, JANE, MARY as the values, the WHERE clause needs to know that it'll use LIKE and IN operator

Example 3: If user were to input SNOOPY, DOG as the values, the WHERE clause needs to know that it'll use IN operator

Upvotes: 0

Views: 110

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5090

There is lot of string manipulation in your requirement.It is always best to do all the string manipulation in front end.

If there will be 3 fix criteria then it can be written as

Create proc DynamicSearch
@flg1 int, --Like
@Search1 varchar(100),
@flg2 int,--Exact
@Search2 varchar(100),
@flg3 int,--IN
@Search3 varchar(100)
as
Begin
if(@flg=1)
begin

--put the search result in #temp table
end
if(@flg=2)
begin

--put the search result in same #temp table
end
if(@flg=3)
begin

--put the search result in same #temp table
end
select * from #temp
end

Otherwise in front end you have to construct as string like this,

'ABC-1|WOLF-2|''BIRD'',''CAT''','DOG''-3' Dont put like operator in string.This string is not final,while coding you can add,substrcact logic.

Then proc will appear roughly like this,

Create proc DynamicSearch
--'ABC-1|WOLF-2|''BIRD'',''CAT''','DOG''-3'
@Search varchar(300)

as
Begin
--- then split string on '|' using any string split function
-- and do string manipulation to seperate'-'
-- This is rough code
declare @Searchpara table(SearchCol varchar(50),flg int)
insert into @Searchpara(SearchCol,flg)
select * from string_split('@Search','|')

-- Then final Search
Select * from maintable mt
where exists(select 1 from @Searchpara where flg=1 and mt.column like '%'+searchcol+'%')
union all
Select * from maintable mt
where exists(select 1 from @Searchpara where flg=2 and and mt.column =searchcol)
union all
Select * from maintable mt
where exists(select 1 from @Searchpara where flg=3 and and mt.column =searchcol)
end

This is only idea and not final code.

Upvotes: 0

Related Questions