Using a tristate parameter in a stored procedure

Question

What is the correct way to do this? For example, how would I change a stored procedure with this signature:

CREATE PROCEDURE dbo.MyProcedure
  @Param BIT = NULL
AS
  SELECT *
  FROM dbo.SomeTable T
  WHERE T.SomeColumn = @Param

So that giving @Param with a value of 1 or 0 performs the filter, but not specifying it or passing NULL performs no filtering? Thank you.

Answer 1

Assuming that NULL means "don't care" then use

CREATE PROCEDURE dbo.MyProcedure 
   @Param BIT = NULL
AS
   SELECT *
   FROM dbo.SomeTable T
   WHERE T.SomeColumn = @Param OR @Param IS NULL

Answer 2

I happen to think that the cleanest way of doing this is (in T-SQL) is:

SELECT * FROM TABLE WHERE column = ISNULL(@param, column)

Other RDBMS would prefer COALESCE instead of ISNULL.

I think it's more obvious what the intent is here, especially as you start to add other OR clauses, and it also keeps you from needing parens when combining with AND clauses.

In my (very) limited testing, there was also a negligible perf increase using ISNULL versus OR @p IS NULL. Not that I'm advocating using ISNULL because of the perf increase (which is extremely marginal at best, and is subject to very specific cases at worst) but it's nice to know it doesn't have a significant cost. Frankly, I'm not sure why it'd make a difference either way, but the execution plan shows about a 1% difference in the filter cost.

Answer 3

There's more than one way. Here's one:

SELECT *
  FROM dbo.SomeTable T
  WHERE T.SomeColumn = COALESCE(@Param, T.SomeColumn)

but this will not include rows for which T.SomeColumn is NULL.

The following alternative will include those rows:

SELECT *
  FROM dbo.SomeTable T
  WHERE T.SomeColumn = @Param OR @Param IS NULL

but it has the disadvantage of the repeated parameter, which is not nice in case you're using another way to pass in parameters, for example, using a placeholder.