将单个 sql server 行存储在变量中，然后使用列值构建查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9172298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Store a single sql server row in variable and then use the column values to construct a query
提问by Moons
I have a problem where the user has set his preferences in a table.
我有一个问题，用户在表格中设置了他的首选项。
The table has more than 5 columns. Now I want to search the other view with condition values that are in the preferencetable.
该表有 5 列以上。现在我想用preference表中的条件值搜索另一个视图。
Example:
例子：
The Preferencetable has price range, colouretc
该Preference表有price range，colour等
So I need to search the products with price and colours.
所以我需要搜索带有价格和颜色的产品。
Now I want to do it in SQL Server itself i.e. passing the preference id, it will always return a single row and then from the columns get the values like min price, max priceand then create a search query.
现在我想在 SQL Server 本身中执行此操作，即传递首选项 ID，它将始终返回单行，然后从列中获取类似 的值min price，max price然后创建搜索查询。
The Issue is I dont know that how I can store the preference row column values in variables so that I can use it.
问题是我不知道如何将首选项行列值存储在变量中以便我可以使用它。
I am using Entity Framework so that I cannot using Dynamic SQL too.
我正在使用实体框架，因此我也不能使用动态 SQL。
I only want to know the way by which I can store the column values of preference table.
我只想知道我可以存储首选项表的列值的方式。
I only know that i can do it something like:
@colour = Select Top 1 Colour from preferences;
我只知道我可以这样做：
@colour = Select Top 1 Colour from preferences;
But like this I need to write this query for every variable. Is there is some better way with something called as CTE etc.
但是像这样我需要为每个变量编写这个查询。是否有一些更好的方法称为 CTE 等。
回答by Thit Lwin Oo
You have to declare the variables, then you can use them to assign the values in the SELECTand use them later:
您必须声明变量，然后您可以使用它们来分配 中的值并在SELECT以后使用它们：
-- declare the variables
DECLARE @min_price decimal(8,2)
DECLARE @max_price decimal(8,2)
DECLARE @color varchar(20)
-- assign the values into the variables
SELECT
@min_price = MIN_PRICE
, @max_price = MAX_PRICE
, @color = Colour
FROM Preference
WHERE preference_id = (parameter of prefrence id)
-- Now you get all 3 values of Colour, Min Price, Max Price,
-- then these 3 values can be used for your query
SELECT *
FROM products
WHERE colour = @color
AND price BETWEEN min_price AND @max_price
Or you can make a single query if these 3 variables are not used elsewhere:
或者，如果这 3 个变量未在其他地方使用，您可以进行单个查询：
SELECT *
FROM products p
INNER JOIN
(
SELECT Colour, MIN_PRICE, MAX_PRICE
FROM Preference
WHERE preference_id = (parameter of prefrence id)
) pre ON (p.colour = pre.Colour AND p.price BETWEEN pre.MIN_PRICE AND pre.MAX_PRICE)
