SQL Server: where-clause dynamic clause

Question

Problem:

Ajax offers a search on [n] ingredients in recipes. That is: to compare recipes with several ingredients.

For example: SELECT Recipes using "flower", "salt" will create: "Pizza", "Bread", "Saltwater" , etc.

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 creating my request using ASP.NET C # due to the dynamic nature of the WHERE .

I bit that I need to build a query in my code layer instead of using a stored procedure / pure SQL, which theoretically should be much faster.

Do you have any thoughts on how I would move all the logic from my code layer to pure SQL, or at least how can I optimize the performance of what I'm doing?

I think of the lines of temporary tables:

Step One : SELECT IngredientsID FROM Ingredients and INSERT INTO temp-table

Step Two : SELECT RecipesName FROM Recipes connected to IngredientsRecipes connected to temp-table.IngredientsID

Answer 1

You have two options. If you are using SQL Server 2008 (or Oracle), you can pass a table value parameter .

If you are using SQL Server 2005, you can use XML to simulate this feature.

If you use something earlier than 2005, you need to combine the identifiers in one line and create UDF for their analysis.

Answer 2

You could at least parameterize the clausule clause to avoid SQL injection, something similar:

 using System.Data; using System.Data.SqlClient; using System.Text; class Foo { public static void Main () { string[] parameters = {"salt", "water", "flower"}; SqlConnection connection = new SqlConnection (); SqlCommand command = connection.CreateCommand (); StringBuilder where = new StringBuilder (); for (int i = 0; i < parametes.Length; i++) { if (i != 0) where.Append (","); where.AppendFormat ("@Param{0}", i); command.Parameters.Add (new SqlParameter ("Param" + i, parameters [i])); } } } 

Answer 3

Depending on how you process the input components, I think this current method has some risks of sql injection.

You can add a component name to the join conditions, which can be faster.

You can also use hash combinations of recipe ingredients for quick searches.