Stored procedure with unknown numer of paramters, how?

[Igor]

I have one table with categories

tblCategories

cat_id | cat_name

-----------------

1 | cat 1

2 | cat 2

3 | cat 3

4 | cat 4

5 | cat 5

6 | cat 6

and one table with projects which relates to tblCategories

tblProjects

proj_id | proj_name | cat_id

----------------------------

1 | proj 1 | 2

2 | proj 2 | 2

3 | proj 3 | 3

4 | proj 4 | 2

How would you create stored procedure for searching some string in
filed proj_name but within multiple categories

for example

CREATE PROCEDURE [spSearch]

(

@SEARCH_STRING nvarchar(200),

@CAT_ID int

)

AS

BEGIN

SELECT proj_id

FROM tblProjects

WHERE (proj_name LIKE '%' + @SEARCH_STRING + '%') AND (cat_id =
@CAT_ID)

END

But that one works only with one categorie and i need to search for one
or more categories at once, does anyone have a solution? Is there
something like ellipsis (...) in C++ for MSSQL?

[Tom Moreau]

Feed the list of categories to the proc in the form of an XML doc. You can
then use OPENXML (SQL 2000) and join onto it. In SQL 2005, you can use
XQuery to do the same.

http://msdn.microsoft.com/library/de...ml/sql01c5.asp

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Igor" <je*******@gmail.com> wrote in message
news:11*********************@z34g2000cwc.googlegro ups.com...
I have one table with categories

tblCategories

cat_id | cat_name

-----------------

1 | cat 1

2 | cat 2

3 | cat 3

4 | cat 4

5 | cat 5

6 | cat 6

and one table with projects which relates to tblCategories

tblProjects

proj_id | proj_name | cat_id

----------------------------

1 | proj 1 | 2

2 | proj 2 | 2

3 | proj 3 | 3

4 | proj 4 | 2

How would you create stored procedure for searching some string in
filed proj_name but within multiple categories

for example

CREATE PROCEDURE [spSearch]

(

@SEARCH_STRING nvarchar(200),

@CAT_ID int

)

AS

BEGIN

SELECT proj_id

FROM tblProjects

WHERE (proj_name LIKE '%' + @SEARCH_STRING + '%') AND (cat_id =
@CAT_ID)

END

But that one works only with one categorie and i need to search for one
or more categories at once, does anyone have a solution? Is there
something like ellipsis (...) in C++ for MSSQL?

[Igor]

OK, thanks i'll try that.

[Erland Sommarskog]

Igor (je*******@gmail.com) writes:

But that one works only with one categorie and i need to search for one
or more categories at once, does anyone have a solution? Is there
something like ellipsis (...) in C++ for MSSQL?

No. There are a couple of methods to pass lists of data to SQL Server. Tom
mention XML. For a single list, that may be a little heavy-handed. Check out
http://www.sommarskog.se/arrays-in-s...st-of-integers for
one alternative. Read the rest of the article to see some more.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Plese stop puttign those silly prefixes on data element name -- it
makes you look like an OO programmer who never read ISO-11179 standard
or took a course in data modeling. The name "tblProjects implies that
you are working on furniture :) Likewise, a procesure named "search"
does not tell us what is being searched. An attriburte cannot be both
a category and an identifier -- did you mean project categories?

Did you know that a T-SQL Procddure can handle over 1000 parameters?
You can probabl;y get by with a mere 50 of them.

CREATE PROCEDURE SearchProjects
(@my_project CHAR(15), @cat01 INTEGER, @cat02 INTEGER, ., @cat99
INTEGER)
AS
BEGIN
SELECT proj_id
FROM Projects
WHERE proj_name LIKE '%' + @my_project + '%'
AND proj_cat IN
(@cat01, @cat02, ., @cat99);
<<error handling here>>;
END;

[Tony Rogerson]

> Did you know that a T-SQL Procddure can handle over 1000 parameters?

You can probabl;y get by with a mere 50 of them.
Get real, that is the worst way to do it.

Pass in a set of data or XML and do it efficiently and think of scalability.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com... Plese stop puttign those silly prefixes on data element name -- it
makes you look like an OO programmer who never read ISO-11179 standard
or took a course in data modeling. The name "tblProjects implies that
you are working on furniture :) Likewise, a procesure named "search"
does not tell us what is being searched. An attriburte cannot be both
a category and an identifier -- did you mean project categories?

Did you know that a T-SQL Procddure can handle over 1000 parameters?
You can probabl;y get by with a mere 50 of them.

CREATE PROCEDURE SearchProjects
(@my_project CHAR(15), @cat01 INTEGER, @cat02 INTEGER, ., @cat99
INTEGER)
AS
BEGIN
SELECT proj_id
FROM Projects
WHERE proj_name LIKE '%' + @my_project + '%'
AND proj_cat IN
(@cat01, @cat02, ., @cat99);
<<error handling here>>;
END;

[Doug]

celkos answer = GAG

GAG is an advanced sql topic that can be best summarized as the sound
the cat makes when regurgitating fur balls.

an elegant way where you have only one data element, but multiple
occurances is to pass the name of a temp table containing the data.
another way is to pass a delimited list - ugly code for sql to parse it
out, but multiple example are out there for leveraging and it is bug
free long term.

[Igor]

I used what Erland Sommarskog suggested, it works fine.
Thanks all for your help.
---
"Plese stop puttign those silly prefixes on data element name -- it
makes you look like an OO programmer who never read ISO-11179 standard
or took a course in data modeling."
actually i didn't read that standard and didn't go on course in data
modeling :)

"Likewise, a procesure named "search" does not tell us what is being
searched."
i was just trying to simplify procedure names and table fields so my
question would not get to complicated...