sql query for nullable number greater than parameter

Question

Simple table:

create table Items
(
  Price money null
)

Now I need to create a stored procedure that accepts one paramter of type bit @ItemsWithPriceTenDollarsOrMore which:

1. returns all items if parameter is null
2. returns all items with Price >=10 if parameter = 1
3. returns all items with Price < 10 if parameter = 0

I have difficulty expressing this filter in a single where statement (not using dynamic sql or conditional logic).

Answer 1

Try this one:

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