Just pass all your parameters in as one array and then split them using one
of the techniques in this article:
http://www.sommarskog.se/arrays-in-sql.html

All the examples in the article join using equality, but you can easily
change that to LIKE.

--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]

Vince
Create a second parameter for stored procedure

SELECT <col lists> FROM Table WHERE col LIKE @par1 OR col LIKE @par2


[quoted text, click to view]

Vince
I think in that way you will have to build dynamic sql.
As you know SQL Server does not support arrays as parameters for stored
procedures.Look at below example, I am sure it will give an idea and you
solve the problem.

CREATE PROCEDURE sparray
@array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @nsql nvarchar(4000)
SET @nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @array + ')'

PRINT @nsql
EXEC sp_executesql @nsql

END
GO

---Usage
EXEC sparray
@array = '''sysobjects'',''sysindexes'',''syscolumns'''
GO




[quoted text, click to view]

Vince
Yes , I assumed that you have to modify the script.
Look at this example does dymanic search written by Erland.

CREATE PROCEDURE search_orders_1
@orderid int = NULL,
@fromdate datetime = NULL, --
@todate datetime = NULL, --
@minprice money = NULL, --
@maxprice money = NULL, --
@custid nchar(5) = NULL, --
@custname nvarchar(40) = NULL, --
@city nvarchar(15) = NULL, --
@region nvarchar(15) = NULL, --
@country nvarchar(15) = NULL, --
@prodid int = NULL, --
@prodname nvarchar(40) = NULL, --
@debug bit = 0 AS --
14
--
15
DECLARE @sql nvarchar(4000), --
16
@paramlist nvarchar(4000) --
17
--
18
SELECT @sql = --
19
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, --
20
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region, --
21
c.PostalCode, c.Country, c.Phone, p.ProductID, --
22
p.ProductName, p.UnitsInStock, p.UnitsOnOrder --
23
FROM Orders o --
24
JOIN [Order Details] od ON o.OrderID = od.OrderID --
25
JOIN Customers c ON o.CustomerID = c.CustomerID --
26
JOIN Products p ON p.ProductID = od.ProductID --
27
WHERE 1 = 1' --
28
--
29
IF @orderid IS NOT NULL --
30
SELECT @sql = @sql + ' AND o.OrderID = @xorderid' + --
31
' AND od.OrderID = @xorderid' --
32
--
33
IF @fromdate IS NOT NULL --
34
SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate' --
35
--
36
IF @todate IS NOT NULL --
37
SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate' --
38
--
39
IF @minprice IS NOT NULL --
40
SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice' --
41
--
42
IF @maxprice IS NOT NULL --
43
SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice' --
44
--
45
IF @custid IS NOT NULL --
46
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' + --
47
' AND c.CustomerID = @xcustid' --
48
--
49
IF @custname IS NOT NULL --
50
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' --
51
--
52
IF @city IS NOT NULL --
53
SELECT @sql = @sql + ' AND c.City = @xcity' --
54
--
55
IF @region IS NOT NULL --
56
SELECT @sql = @sql + ' AND c.Region = @xregion' --
57
--
58
IF @country IS NOT NULL --
59
SELECT @sql = @sql + ' AND c.Country = @xcountry' --
60
--
61
IF @prodid IS NOT NULL --
62
SELECT @sql = @sql + ' AND od.ProductID = @xprodid' + --
63
' AND p.ProductID = @xprodid' --
64
--
65
IF @prodname IS NOT NULL --
66
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' --
67
--
68
SELECT @sql = @sql + ' ORDER BY o.OrderID' --
69
--
70
IF @debug = 1 --
71
PRINT @sql --
72
--
73
SELECT @paramlist = '@xorderid int, --
74
@xfromdate datetime, --
75
@xtodate datetime, --
76
@xminprice money, --
77
@xmaxprice money, --
78
@xcustid nchar(5), --
79
@xcustname nvarchar(40), --
80
@xcity nvarchar(15), --
81
@xregion nvarchar(15), --
82
@xcountry nvarchar(15), --
83
@xprodid int, --
84

If I have a procedure like (say):

CREATE procedure SP_ABC(@PAR1 varchar(20) as
select * from TABLE where [COL] like @PAR1

If I supply sp_ABC(111%), I get all rows where COL begins with 111.

Now, I would like to pass multiple values to it, in other words I need to be
able to say sp_ABC(111%,112%) meaning select * from TABLE where [COL] like
111% or [COL] like 112%.


Is there any way I can do this without using DSQL? The number of such values
could vary. I tried injecting values into it from the client side
application, like sending 111% OR [COL] LIKE 112% but that did not work.

Any better solution?

Thanks a lot.
Vince

THanks Uri and Mal for your response but I don't know how many parameters
there are. For instance, if the user chooses to get information pertaining
to teacher Angel and James, I could have two parameters. But, there are
around 10 teachers and over 20 levels and the user could choose any
combination of them. This is why I ruled out using more parameters.

Also, Mal, your method requires logic in regex. I can only say [A-Za-z] with
the teachers. What if the user wants information pertaining to Angel, Andrew
and James. It's hard to write all combinatations in regex and would be easy
if I could say where [COL] like 'James%' or [COL] like 'Angel%' or [COL]
like 'Andrew%'. If the user looks up only one teacher, I would have to make
the remaining two choices like '%%'. Is that the only way out? Declare a
maxmimum numer of parameters and if there is a value, send them or otherwise
use %%? Is this the best way? The reason why I need this in a stored
procedure is because I am using Cross Tab reports after the records are
obtained.

Thanks a lot for responding.

Vince

[quoted text, click to view]

Uri,
Thanks for your quick response. The problem with the IN statement is that I
cannot do IN ('1%') unlike LIKE where I can do '1%'. I guess the best
solution is to declare some 20 parameters and set them to '%%' if there is
no value. If there is a value, I can put that in. I suppose there it won't
be such a hinder on performance.

Out of curiosity, why doesn't this work:
where ([COL] LIKE @Hello)

where @Hello is : 1% or [COL] like '2%

I thought this would be intrepreted as:
where ([COL] like '1% or [COL] like '2%'

I understand that this would be more on the realms of Dynamic SQL but was
wondering how SQL intreprets such variables. Also, I read something about
SQL Injection, isn't this similar to that?

Vince

[quoted text, click to view]

Thanks a lot Uri and Jacco. I'll study the examples.

Vince
[quoted text, click to view]

Thank you all.

I tried Jacco's method:
Step 1.
CREATE FUNCTION iter_charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END

SET @leftover = @tmpstr
END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END

Step2:
In my stored procedure, I have something like this:

create procedure Sp_PRO1(@Values varchar(200)) as
select (blah blah many cases and all that for cross tab reports)
where [level] like iter_charlist_to_table(@Values,DEFAULT)

Each time I try to compile Step 2, I get a "Incorrect syntax near DEFAULT"
or "Not recognized function name iter_charlist_to_table". I know that what I
am doing doesn't look right (gut feeling) but have no idea how to fix it.

I hope to call the function later using sp_pro1('1%,2%,3%') so that I get
some query that ends with where [level] like '1%' or [level] like '2%' and
so on.

Can somebody please help?

Thanks,
Vince


[quoted text, click to view]

You need to prefix the function name with the owner name, i.e.:

where [level] like dbo.iter_charlist_to_table(@Values,DEFAULT)

That's just a syntactical requirement.

--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]

A real great method Jacco. I did this and it has saved me plenty of recoding
/ thinking (I did not bother understanding the split function) and it works
great! Thanks again! I would recommend this method to anybody who wishes to
split a huge string into smaller one!

declare @Level varchar(50)
set @Level='1%,2%'
select * from studentscheduledetails as A,
iter_charlist_to_table(@Level,DEFAULT)as B where a.[level] like b.str


Vince
[quoted text, click to view]