I'm working on a website that will be unveiled in the next few weeks.  Yearly revenue will probably bring in something between bupkis and zilch; not quite Google's numbers yet.  The site is written in C# with MS SQL 2005 on the backend.  I'm trying to conserve the limited bits and bytes in the universe and wanted to make my code as reusable as possible. 

For the sake of an example, we'll say this site allows person A to send something to person B.  Person A should be able to log in to the site and see everything they sent to A.  They should also be able to see everything someone else (person C) has sent to them.  The same query is used but the where clause is different.  I don't want to type the stored procedure twice, and I certainly don't want to maintain it twice so I went searching for a dynamic where statement within a stored procedure and I present to you my geeky friends the answer: COALESCE

Find out how to do this after the break....

All examples below will use a single table (tblExample) like so:

To get all records that Person A has given:

select * from tblExample where From='Person A'

To get all the records that Person A has recieved:

select * from tblExample where To='Person A'

To combine them in to one single stored procedures we would do.... wait, hold the brakes!  Not so fast!  First, what is this COALESCE?

What does COALESCE do for me?

There is more than one use for it but in my example, you pass it a list of values and it will return the first one that is not null.  Example time!

select COALESCE (null, null, 2, null, null, 5)

This will return the number 2 since it is the first non null value passed.

Come one, give me the example for the dynamic where clause already!

OK, you deserve it.  Well, after a little different example.

If you run this:

select * from tblExample where From=From

You will get all rows because on each row, From is equal to itself... duh!  Do you see where I'm going yet?

And now for the actual method I used

create procedure dbo.usp_ItsAboutTime (

    @FromName varchar(20) = null,

    @ToName varchar(20) = null )

AS

    select * from tblExample

    where FROM = COALESCE(@FromName, FROM) and

              To = COALESCE(@ToName, To)

GO

To get everything from person A:

exec usp_ItsAboutTime @FromName='Person A'

To get everything given to person A:

exec usp_ItsAboutTime @ToName='Person A'

Why does it work?

 It works because the arguments are optional.  If you don't pass one of the arguements we set it to null.  Since we put in our argument as the first in the COALESCE list, it skips it if we didn't supply and and returns the column we are searching on which is always true.

My actual stored procedure is 200+ lines long so you can see why I went looking for this elegant solution.