|
Home > Archive > Microsoft SQL Server forum > April 2006 > Stored procedure with unknown numer of paramters, how?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored procedure with unknown numer of paramters, how?
|
|
|
| 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 2006-03-29, 7:29 am |
| 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/d...tml/sql01c5.asp
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Igor" <jerosimic@gmail.com> wrote in message
news:1143628023.313215.26320@z34g2000cwc.googlegroups.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?
| |
|
| OK, thanks i'll try that.
| |
|
|
| --CELKO-- 2006-03-30, 9:30 am |
| 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 2006-03-31, 3:30 am |
| > 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--" <jcelko212@earthlink.net> wrote in message
news:1143728606.490777.142380@i39g2000cwa.googlegroups.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;
>
| |
|
| 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.
| |
|
| 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...
|
|
|
|
|