How to write filtered / optional queries using SQL stored procedures?

When writing 'select' statements, we often need optional sets of paramaters for filtering the query.

For example, we may need to write a stored procedure that will return all records for a given Creation Date and/or a specific catagory.

SELECT * FROM [tableName] WHERE CreatedDate = @CreatedDate AND CategoryId = @CategoryId

If we want to return all records, we would right another stored procedure with the following select statement

SELECT * FROM [tableName]

But, instead, we can write one stored procedure that can do both of the above and then some.

CREATE PROCEDURE [spGetTableFiltered]

 @Id int = NULL,

 @CreatedDate datetime = NULL,

 @CategoryId varchar(3) = NULL

AS

BEGIN

 SELECT

  * 

 FROM

  [tableName]

 WHERE 

 (@Id IS NULL OR Id = @Id) AND

 (@CreatedDate IS NULL OR CreatedDate = @CreatedDate) AND

 (@CategoryId IS NULL OR CategoryId = @CategoryId)

END

Alternatively, The WHERE clause can be write this way instead:

WHERE  

 ISNULL(Id, @Id) AND

 ISNULL(CreatedDate, @CreatedDate) AND

 ISNULL(CatagoryId, @CatagoryId)

If we don't pass any parameters to this store procuder, the result will bring back all records.  Similarly, if we pass nulls for each parameter, all records will be returned.

Only the parameters passed will be used to filter the query.

When using C# 2.0 .NET framework, some types will need to be declared as "Nullable" to be able to utilize this technique.  For example:

Nullable myDateTime = new DateTime();