将多个语句之间传递给存储过程 [英] Passing Multiple Between Statements To Stored Procedure

问题描述

我有一个装满产品的表，之前我们将 MaxPrice 和 MinPrice 传递给存储过程并选择价格介于两个值之间的产品.

I have a table full of products, previously we passed a MaxPrice and MinPrice to the stored procedure and selected products with the price between two values.

但现在我想传递多个范围值并想选择价格在多个范围之间的产品.

But now I want to pass multiple range values and want to select products that their prices are between multiple ranges.

假设我有一个这样的存储过程:

Let's say I had a stored procedure like this:

@PriceMin decimal(18, 4) = null,
@PriceMax decimal(18, 4) = null,

... 
WHERE 
    product.Price > @PriceMin 
    AND product.Price < @PriceMax

但现在我想根据用户选择传递多个范围的值，并有一个这样的选择:

but now I want to pass multiple range of values based on user selection and have a select like this:

WHERE 
    (product.Price > @PriceMin1 AND product.Price < @PriceMax1)
    OR (product.Price > @PriceMin2 AND product.Price < @PriceMax2)
    OR (product.Price > @PriceMin3 AND product.Price < @PriceMax3)
...

我该怎么做?

推荐答案

我假设您不知道将要传递多少范围，因此我将使用表值参数来传递数据到存储过程.

I am going to assume that you don't know how many ranges are going to be passed up front, so I'd take up table value parameter to pass data to the stored procedure.

第 1 步:创建 TVP

CREATE TYPE dbo.Ranges AS TABLE  
    ( PriceMin  decimal(18, 4), PriceMax   decimal(18, 4) )  

第 2 步:修改您的存储过程参数列表和代码

Step 2: Modify your stored procedure parameter list and code

ALTER PROCEDURE usp_selectBasedOnPrice   
    (@rangeList dbo.Ranges READONLY)  
BEGIN
..
-- from product
-- WHERE product.Price > @PriceMin and product.Price < @PriceMax

from product p JOIN @rangeList r
 on p.Price  BETWEEN r.PriceMin  AND r.PriceMax 
END

PS:请注意，BETWEEN 比 > 和 < 语句要好，在这种情况下，如果您的价格范围包含在内，即如果你实际上需要 <= 和 >=;并且 JOIN 比多个 WHERE 子句

PS: Note that BETWEEN is better than > and < statement,in this case if your price ranges are inclusive i.e. if you actually need <= and >=; and JOIN is much better than multiple WHERE clauses

请注意 BETWEEN 相当于 <= , >= 而不是 <, >

Please do note that BETWEEN is equivalent to short hand for <= , >= and not <, >

MS 文档在 TVP 上

这篇关于将多个语句之间传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！