Combining Like Searche with in search

while writing SP for Reports

1. MultiValue Parameter with Exact search Criteria:

There are conditions where you have have to pass parameters which are
multivalues usually string of comma separated values like @ProductID='Ram, Krina,
Hanuman'.

When we have to do exact search on these parameters we can directly use a
where clause like

p.ProductID in (Select _ID from dbo.fnSplitString(@ProductID,','))

where fnSplitString is splits values of parameter into table. exact search
can be performed. (code at the bottom)

2. Single Value Parameter with like Search.

In this situation we pass string parameter with a single value and
pass value like @Name='Ram'
the where clause will be

st.swName like (@Name+'%')

and all the names starting with Ram will be returned

3. Multi Value Parameter with like Search.

Now searching on this option how to do this. in nSQL



Code for fnSplitString

CREATE FUNCTION [dbo].[fnSplitString]
( @InputString varchar(8000), @SplitChar char(1) )

RETURNS @MyTbl TABLE(_ID varchar(4000))ASBEGIN

declare @LENInputString as intdeclare @x as int
declare @SingleChar as varchar(4000)

set @LENInputString = len(@InputString)
set @x = 0
while (select @x) < @LENInputString
begin set @SingleChar = SUBSTRING(@InputString, @x, 1)
if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
begin
declare @y as int
set @y = @x+1
while (select @y) < @LENInputString+1
AND (select SUBSTRING(@InputString, @y, 1)) <> @SplitChar
begin
set @SingleChar = @SingleChar + SUBSTRING(@InputString, @y, 1)
set @y = @y+1
end
set @x=@y
end

if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
begin
set @SingleChar = ltrim(rtrim(@SingleChar))
INSERT INTO @MyTbl (_ID) VALUES (@SingleChar)
end
set @x = @x+1
end
return
end