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.

Nice little trick if you want to return results sorted randomly from a SQL 2000/2005 database:

SELECT * FROM Customers ORDER BY newid()

So say you want to return 10 random customers from the database, you'd modify the statement as such:

SELECT TOP 10 * FROM Customers ORDER BY newid()

For those who don't know, newid() generates a new guid, just as Guid.NewGuid() does in .NET.  By ordering a uniquely generated guid, we're guaranteed random results.

Well it was bound to happen.  Everyone in my office had made the switch back, I was the last holdout.  I finally had it with Vista! 

I really wanted to like it, I really did.  It looked sharp... hmm, come to think of it, that's all I can really say about it.  I looked sharp.  But in the end, after installing some updates, it became unstable and I failed to figure out what the cause was.  Even with uninstalling said updates, it still randomly spiked the cpu to 100% for 5 or so minutes at a time, leaving me helpless to do any work.

So, before I left work yesterday I repartinioned my C:\ drive and reinstalled XP.  Up until 11pm last night I was trying to get my machine back to par for work.  I have to admit, after making the switch from XP to Vista then back to XP, I do notice that XP is considerably faster.  I guess making it look "sharp" had it's disadvantages.

Quick note to Microsoft: WTF MAN!  How could you screw up an OS so badly?  I want Vista, but I want it STABLE.  I don't want to have to cross my fingers before installing updates as I had to ever since I installed it.  And yes, I have had to uninstall updates in the past. 

Here's another WTF that Vista threw at me today.  I had to come home early today and decided to work the rest of the day from home.  Now, because I can't undock my laptop because Vista freezes, before I leave each day, I put it to "sleep".  So, first thing I needed to do before I could resume working was to connect to my works VPN.  Now, normally this is not an issue, but sometime (I'd say every 1 out of 4 times) Vista displays "This connection requires an active Internet connection" in the "Connect to a Network" dialog even though, directly below it, it clearly shows that I'm connected via my wireless connection. 



To get around this, simple do the following:

Click on the "Set up a connection or network" link on the above dialog box.



Scroll down the list of connection options and select "Connect to a workplace".



Choose "Yes, I'll choose an existing connection" on the next screen and Vista will connect to the VPN.

Not sure why this happens but I've seen it enough times to warrant this post.

Yesterday, my wife and I went out to our favorite christmas tree farm and picked out three lovely trees.  Yes, I said three.  We do this. 

The farmer was nice enough to let my wife cut down the smallest of them.



Exhilarating! Her first confirmed kill.



After all that hard work, here she is in her "pose" under a warm cup of Peet's Coffee.