将多个语句传递给存储过程

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.

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

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)
...

How can I do this?

我该怎么做呢?

1 个解决方案

#1

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.

我将假设您不知道前面将会传递多少个范围，因此我将使用表值参数将数据传递到存储过程。

Step 1: Create a TVP

步骤1:创建TVP

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

Step 2: Modify your stored procedure parameter list and code

步骤2:修改存储过程参数列表和代码。

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: 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

PS:注意BETWEEN要优于>和< statement，在这种情况下，如果你的价格范围是包含在内的，也就是说，如果你确实需要<=和>=;而且JOIN要比多重WHERE子句好得多

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

请注意BETWEEN对于<=，>=和not <， >是等价的

MS documentation on TVP

利用文档女士

#1