Many times in many projects I had to filter some records in a list by selecting not one but multiple categories. This has been a nightmare to be done in a stored procedure since you had to have a limited numbers of parameters and it would be inefficient to have @category1, @category2, etc. What you wanted to do is have one value that you would name @categories and then pass there any number of values separated by a character.
Now this can be achieved by using an intermediate stored procedute that converts a string delimited by a char to a table list of values. This stored procedure is the following:
CREATE FUNCTION dbo.spListToTable ( @List VARCHAR(MAX), @Delim CHAR ) RETURNS @ParsedList TABLE ( item VARCHAR(MAX) ) AS BEGIN DECLARE @item VARCHAR(MAX), @Pos INT SET @List = LTRIM(RTRIM(@List))+ @Delim SET @Pos = CHARINDEX(@Delim, @List, 1) WHILE @Pos > 0 BEGIN SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))) IF @item <> '' BEGIN INSERT INTO @ParsedList (item) VALUES (CAST(@item AS VARCHAR(MAX))) END SET @List = RIGHT(@List, LEN(@List) - @Pos) SET @Pos = CHARINDEX(@Delim, @List, 1) END RETURN END GO
Then in an example as the one described above you would use it like this :
SELECT * FROM [tblList] WHERE [categoryID] IN ( SELECT [item] FROM dbo.spListToTable('1,2,3',',') )
That would return you all the items that are in categories 1,2,3 !