Recently, I uninstalled Vista and reinstalled XP on my work laptop.  Upon doing so, I had to reinstall all of my developer tools.  One of them was Visual Studio 2005.  Oddly enough, When I installed Firefox I opted to have it run as my default browser (when I was on Vista I had IE).  It didn't occur to me that this would cause any problems.  That was until I started debugging applications.

At first, all seemed fine.  Actually I thought it was going really good because after stopping the debugger, the web page remained open, something that didn't happen with IE.  But, as time went on, I found that my system slowed down once I started debugging anything.  Looking at the processes, I found that Firefox was using about 2 GB's of RAM.  Try sucking that into Visual Studio to debug.  Yeah, that'll cause some slow downs.  

So, I decided that, as much as I like Firefox, I'll set IE as my default browser.  Unfortunately, after doing this, Visual Studio kept debugging with Firefox and causing havoc with my machine.  So, I did a quick search and found this obscure forum thread that explained how to reset Visual Studio's browser.  And it's pretty simple.

First, open a web project.  Find any .aspx page and right click it and select "Browse with.."



There you should see a list of all available browsers plus the built in one.



Select IE from the list and click "Set as Default".



Close either by clicking "Browse" or "Cancel".  It doesn't matter which because the setting has been saved by this point.

Debug your application and notice that IE now opens up.  And much faster I might add.

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.