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)
...
How can I do this?