Passing multiple filtering parameters to a stored Proc

Question

Hi, I am sure the title of my question doesn't make much sense. But here is what I'm trying to achieve. I have table with different stories(columns are StoryID, StoryTitle, StoryDesc, StoryCategory), so each story can belong to a category. for example category1, category2,.... category10. I am in the need of a SP, where i specify the categories as parameters(multiple categories, in CSV format or XML) and the SP will return the stories belong to that categories.

I'm not sure what would be the best way to do this in one SP call. Any pointers will be appreciated.

thank you

Answer 1

Call your stored procedure passing the Categories parameters as input parameters.

Inside the procedure's SQL code filter it with:

Select * from Stories where StoryCategoy = category1 OR StoryCategory = category2, etc...

This link will provide you with a better way of doing this...

Passing Arrays to SQL Stored Procedures in C# ASP .NET

Oh, this one is better yet:

Passing lists to SQL Server 2005 with XML Parameters

Some code to ilustrate:

DECLARE @categoryIds xml
SET @categoryIds ='<Categories><id>17</id><id>83</id><id>88</id></Categories>'

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @categoryIds.nodes('/Categories/id') as ParamValues(ID)

Which gives us the following three rows:

17
83
88

Answer 2

1. Create a temporary table

   create table #MySp_Categories (
       categoryId int not null primary key
   )
   

   Insert category list into it and call your procedure (MySp). In MySp join with #MySp_Categories. After procedure finishes drop #MySp_Categories.

   Slightly complicated protocol, but works.

2. Encode list of categories as XML and pass varchar parameter containing XML to your procedure. In procedure use OPENXML in your query.