This one has been plaguing me for years: What is the best way to create a dynamic stored procedure that allows null inputs to filter results?  To solve this, over the years, I've tried a few things, below are just a couple:

• Build a SELECT statement string together concatenating non-null values then execute the string.
• Use IF statements to narrow down the type of filtering requested and run the pre-built SQL statement with the corresponding non-null values.

The problem with the above solutions is that neither rely on one solid select statement.

Today, while working on another request to come up with a dynamic stored procedure for a specific report a client wants, I had an epiphany.  If I use isnull() in conjunction to with the actual value, I can get the desired results, no matter what input(s) are null -if any or all.

Here's the magic line that allows just that:

WHERE D1.MarketID = isnull(@MarketID, D1.MarketID)

Now, we all know that isnull() returns the first non-null value.  So, if @MarketID is null, then we just return the actual value of the row in question; D1.MarketId.  For a fact, D1.MarketID will ALWAYS equal D1.MarketID.  That's it!  

So, if we create a sproc that takes in five different values (filters), all of which can be null, utilize the above isnull() trick, we can let out business layer decide which results to return simply by passing in an actual value or a DBNull.Value to the call.  The stored procedure finally becomes dumb and all of the logic is finally in the hands of the business logic, where it should be, in a non-kludgie (pronounced "Hacked") way.

Here is a quick example:

CREATE PROC Reporting_Impressions
    @MarketID int = NULL,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL,
    @Page varchar(25) = NULL,
    @LocationID uniqueidentifier = NULL
AS

SELECT D1.LocationId, D1.LocationName, D1.MarketId, D1.MarketName,
D1.DisplayedOnPage, count(D1.DisplayedDate) AS TotalImpressions
FROM PremiumMerchant_Displays AS D1 WITH(NOLOCK)
INNER JOIN PremiumMerchant_Displays AS D2 WITH(NOLOCK)
ON D1.LocationId = D2.LocationId AND D1.MarketId = D2.MarketId AND
D1.DisplayedOnPage = D2.DisplayedOnPage AND D1.ImpressionId <> D2.ImpressionId
WHERE D1.MarketID = isnull(@MarketID, D1.MarketID)
AND D1.DisplayedOnPage = isnull(@Page, D1.DisplayedOnPage)
AND D1.LocationId = isnull(@LocationID, D1.LocationId)
AND D1.DisplayedDate >= isnull(@StartDate, D1.DisplayedDate)
AND D1.DisplayedDate <= isnull(@EndDate, D1.DisplayedDate)
GROUP BY D1.LocationId, D1.LocationName, D1.MarketId, D1.MarketName, D1.DisplayedOnPage

GO

And here are some various ways to call this procedure:

--Specific market id
EXEC Reporting_Impressions 4, NULL, NULL, NULL, NULL

--All, no filters
EXEC Reporting_Impressions NULL, NULL, NULL, NULL, NULL

--Date range
EXEC Reporting_Impressions NULL, '1/1/2008 7:00:00 AM', '2/15/2008 11:00:00 AM', NULL, NULL

--Specific page
EXEC Reporting_Impressions NULL, NULL, NULL, 'Home', NULL

--Specific Location
EXEC Reporting_Impressions NULL, NULL, NULL, NULL, 'B40B82C9-A41A-49DA-8950-3CBFF4488AEA'

--Specific market id and page for a given date range
EXEC Reporting_Impressions 4, '1/1/2008 7:00:00 AM', '2/15/2008 11:00:00 AM', 'Directory', NULL

As you can see, there is only one stored procedure with many different results, all of which are dependent on the number of non-null inputs and snuggled within a nice, clean, single select statement.