How can I pull this one with "WHERE IN"

Hello everyone,

For the sake of simplicity let's imagin we have two tables simple tables: Categories(Id, Name) and Products(Id, CategoryId, Name). My question is how can I make a product list, filtered by category, using a Stored Producedure (SP). My objective is to have the SP receiving the list of categories we want to display, as an input parameter, using it in the SQL Statement. Something like:

Procedure:
ListProductsFilteredByCategory
@.Categories AS NVarChar

SELECT
[C.Name] AS CategoryName
,[P.Name] AS ProductName
FROM
Products P
JOIN
Categories C
ON C.Id = P.CategoryId
WHERE IN (@.Categories);

How could I make this SP work in a way that @.Categories can contain an undefined number of category IDs?

Best regards,
DBA

DECLARE @.query NVARCHAR(1000)

SET @.query = 'SELECT [C.Name] AS CategoryName, [P.Name] AS ProductName FROM Products P JOIN Categories C ON C.Id = P.CategoryId WHERE P.CategoryId IN (' + @.Categories + ')'

sp_executesql @.query

|||

smalltalk:

DECLARE @.query NVARCHAR(1000)

SET @.query = 'SELECT [C.Name] AS CategoryName, [P.Name] AS ProductName FROM Products P JOIN Categories C ON C.Id = P.CategoryId WHERE P.CategoryId IN (' + @.Categories + ')'

sp_executesql @.query

Thanks for the hint :)

Just that a question instantly poped into my head: Wouldn't that cause the SQL Server to recompile the whole procedure, hence causing a performance overhead? This query is going to be used againts a considerably large dataset :/

|||

I don't know much about the performance of dynamic SQL. After a google search I came acrossthis article which seems to touch the subject.

PS. In the exemple above you need an EXEC before sp_executesql. I didn't test the code though.

|||

smalltalk:

I don't know much about the performance of dynamic SQL. After a google search I came acrossthis article which seems to touch the subject.

PS. In the exemple above you need an EXEC before sp_executesql. I didn't test the code though.

I've been doing some searches meanwhile and I've came across that same article :)

During my readings, I've discovered that some people say that you should declare a variable, of the type table, and insert the splited list (using a comma delimiter, for example) into that container. This would go arround the recompiling issue, although I've read that some argue that t-sql string functions can cause quite some overhead in large queries.

I will have to do some further reading in order to decide which way is the best. Nevertheless, thanks a lot for your help, smalltalk.