SQL服务器:动态where子句 [英] SQL Server: Dynamic where-clause
问题描述
问题:
阿贾克斯建议搜索上[ N 的]食谱中的成分。即:匹配多种成分配方
Ajax suggest-search on [n] ingredients in recipes. That is: match recipes against multiple ingredients.
例如:用花SELECT食谱,盐
将产生:比萨,面包,咸水
等等。
表:
Ingredients [
IngredientsID INT [PK],
IngredientsName VARCHAR
]
Recipes [
RecipesID INT [PK],
RecipesName VARCHAR
]
IngredientsRecipes [
IngredientsRecipesID INT [PK],
IngredientsID INT,
RecipesID INT
]
查询:
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')
我目前正在建设使用ASP.NET C#我的查询因为 WHERE
子句的动态特性。
我咬,我必须构建在我的code层的查询,而不是使用存储过程/纯SQL,这在理论上应该是更快的。
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.
有你们相处如何将我所有的逻辑中摆脱我的code层纯SQL,或者至少我怎么可以优化我在做什么性能的任何想法?
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?
我沿着临时表的行想:
第一步: SELECT IngredientsID FROM成分
和 INSERT INTO临时表
第二步: SELECT RecipesName从食谱
与 IngredientsRecipes
加入了加入了与温度-table.IngredientsID
Step two: SELECT RecipesName FROM Recipes
joined with IngredientsRecipes
joined with temp-table.IngredientsID
推荐答案
您有两种选择。如果你使用SQL Server 2008(或Oracle),您可以通过在的表值参数。
You have two options. If you're using SQL Server 2008 (or Oracle) you can pass in a table value parameter.
如果你使用SQL Server 2005中,你可以使用<一个href=\"http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx\"相对=nofollow> XML来模拟这种能力
If you're using SQL Server 2005, you can use XML to simulate this capability
如果您正在使用的东西比2005年更早,你需要连接的ID在一个字符串,并创建一个UDF来解析他们。
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.
这篇关于SQL服务器:动态where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!