SQL procedure where clause to list records

Question

I have a stored procedure that will query and return records based on if items are available or not. That part is easy, Im simply passing in a variable to check where available is yes or no. But what I want to know is how do I throw a everything clause in there (i.e available, not available, or everything)?

The where clause right now is

where availability = @availability

The values of availabitility are either 1 or 0, nothing else.

Answer 1

You can use NULL to represent everything.

WHERE (@availability IS NULL OR availability = @availability)

Answer 2

SELECT  *
FROM    mytable
WHERE   @availability IS NULL
UNION ALL
SELECT  *
FROM    mytable
WHERE   availability = @availability

Passing a NULL value will select everything.

This query will use an index on availability if any.

Answer 3

multiple ways of doing it. Simpliest way is is to set the default value of the @availability to null and then your where clause would look like this

WHERE (@availability IS NULL OR availability = @availability)

Answer 4

Don't know the type of @availability, but assuming -1 = everything then you could simply do a

where @availability = -1 OR availability = @availability