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
