Here's one I just had to solve.  I had an xml string that was being passed to a sproc that used sp_xml_preparedocument to insert the values using OPENXML.  Problem was that the sproc was throwing the following error:

'An invalid character was found in text'

The odd thing was that the xml in question was valid in Visual Studio before it passed it to the sproc and did not have any illegal characters.  What I found was that by simply changing the input value from varchar(max) to nvarchar(max) sp_xml_preparedocument was able to parse the xml just fine. 

For a quick refresher, read up on nchar and nvarchar in T-SQL.

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.