SQL Server: Dynamic where-clause
c#, performance, sql-server
Solution
You have two options. If you're using SQL Server 2008 (or Oracle) you can pass in a table value parameter.
If you're using SQL Server 2005, you can use XML to simulate this capability
If you're using something earlier than 2005, you need to concatenate the ids in a single string and create a UDF to parse them.
Problem
Problem: Ajax suggest-search on [n] ingredients in recipes. That is: match recipes against multiple ingredients. For instance: `SELECT Recipes using "flower", "salt"` would produce: `"Pizza", "Bread", "Saltwater"` and so forth. Tables: ``` Ingredients [ IngredientsID INT [PK], IngredientsName VARCHAR ] Recipes [ RecipesID INT [PK], RecipesName VARCHAR ] IngredientsRecipes [ IngredientsRecipesID INT [PK], IngredientsID INT, RecipesID INT ] ``` Query: ``` SELECT Recipes.RecipesID, Recipes.RecipesName, Ingredients.IngredientsID, Ingredients.IngredientsName FROM IngredientsRecipes INNER JOIN Ingredients ON IngredientsRecipes.IngredientsID = Ingredients.IngredientsID INNER JOIN Recipes ON IngredientsRecipes.RecipesID = Recipes.RecipesID WHERE Ingredients.IngredientsName IN ('salt', 'water', 'flower') ``` I am currently constructing my query using ASP.NET C# because of the dynamic nature of the `WHERE` clause. I bites that I have to construct the query in my code-layer instead of using a stored procedure/pure SQL, which in theory should be much faster. Have you guys got any thoughts on how I would move all of the logic from my code-layer to pure SQL, or at least how I can optimize the performance of what I'm doing? I am thinking along the lines of temporary tables: Step one: `SELECT IngredientsID FROM Ingredients` and `INSERT INTO temp-table` Step two: `SELECT RecipesName FROM Recipes` joined with `IngredientsRecipes` joined with `temp-table.IngredientsID`