How pass a list of values to compare in a SQL Function in SQL Server 2008?

Question

I have an SQL Function with the following SQL within:

SELECT StockID FROM (SELECT DISTINCT StockID, 
ROW_NUMBER() OVER(ORDER BY DateAdded DESC) AS RowNum
FROM Stock
WHERE CategoryCode LIKE @CategoryID) AS Info
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

I have a Parameter @CategoryID - however I need to take in a category ID such as "BA" and translated this to a list of Category IDs such as "IE","EG" etc so my WHERE clause looks like:

WHERE (CategoryCode LIKE 'IE' OR CategoryCode LIKE 'EG') AS Info

I have a Lookup Table which contains the "BA" code and then all the real category codes this means such as "IE" and "EG". How do I have the CategoryID expand to multiple "OR" statements in my SQL Function? I am unsure how to do this, can anyone solve this problem?

At the moment the query as shown can cope with one CategoryID such as "IE", this is done as I want a category page such as category.aspx where a parameter "BA" is passed such as category.aspx?category=BA and this page will list all items with the category codes "EG" and "IE".

The reason I need this is there is a "parent" category code which has multiple "children" category codes which are different to the parent code. I am using ASP.NET and .NET 3.5 on the front-end if this helps.

Answer 1

Assuming the parameter is a common delimited list of categoryID's

Try

WHERE charindex(','+CategoryCode+',',','+@CatParam+',') > 0

Performance won't be great, but it should do the trick for you

Answer 2

Try using

WHERE CategoryCode IN (
    SELECT LookupCategoryCode 
    FROM LookupTable 
    WHERE LookupCategoryId = @CategoryId
)

Replacing "LookupCategoryCode", "LookupTable", and "LookupCategoryId" for the respective values in your lookup table.