Sql query for NULL value exceeding parameter

Question

Simple table:

create table Items
(
  Price money null
)

Now I need to create a stored procedure that takes one parameter of type bit @ItemsWithPriceTenDollarsOrMore , which:

• returns all elements if parameter is null
• returns all elements with a price> = 10 if parameter = 1
• returns all elements with a price <10 if parameter = 0

I find it difficult to express this filter in a single where statement (without using dynamic sql or conditional logic).

Answer 1

Try the following:

SELECT * FROM Items
WHERE (@ItemsWithPriceTenDollarsOrMore = 1 AND Price >=10)
OR (@ItemsWithPriceTenDollarsOrMore = 0 AND Price <10)
OR (@ItemsWithPriceTenDollarsOrMore IS NULL)