Re: Stored procedure with unknown numer of paramters, how?
- From: "Tom Moreau" <tom@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 29 Mar 2006 07:06:34 -0500
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/default.asp?url=/library/en-us/dnsqlpro01/html/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@xxxxxxxxx> wrote in message
news:1143628023.313215.26320@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?
.
- Follow-Ups:
- References:
- Prev by Date: Re: Problem with a join due to multiple Nulls
- Next by Date: Re: Stored procedure with unknown numer of paramters, how?
- Previous by thread: Stored procedure with unknown numer of paramters, how?
- Next by thread: Re: Stored procedure with unknown numer of paramters, how?
- Index(es):
Relevant Pages
|
