Executive Summary:
Have you ever had a stored procedure that could accept a NULL value as a parameter but didn't seem to work correctly when you did so? You can handle NULL parameters several ways, one of which is to force the use of default parameter values. This technique presents developers with a handy option when designing applications that use ADO.NET to execute T-SQL stored procedures with optional named parameters. |
A question I often see in public and MVP newsgroups (where I spend far too much time) is, "I have a stored procedure that can accept a NULL value as a parameter, but it doesn't seem to work correctly. What am I doing wrong?" The easiest way to answer this question is with examples. Although the following examples use SQL Server 2008 and ADO.NET 3.5, most of the techniques I'll be showing you will work with earlier versions of SQL Server and ADO.NET (even classic ADO).
To make this simple, let's create the dbo.GetProductsByShipDate stored procedure in Listing 1 in the AdventureWorks2008 sample database.
Listing 1: The dbo.GetProductsByShipDate Stored Procedure |
 |
In this stored procedure, the only input parameter is a date that has the new data type of date. This data type has no time component, which makes testing to see whether a value is an exact date far simpler. Although this data type is new to SQL Server 2008, the dates stored in the AdventureWorks2008 database have the old-fashioned datetime data type, so the stored procedure performs the conversion server-side.
As you can see in Listing 1, the input parameter is set to NULL if no value is sent by the code that invokes the stored procedure, right? Actually, that's inaccurate. The default value is used if a parameter isn't passed in. For example, as Figure 1 shows, the T-SQL query processor knows to substitute NULL for the @DateWanted value when the stored procedure is invoked with no parameters. In other words, by not passing in a parameter, you're forcing the use of the default parameter value.
Figure 1: Results from executing dbo.GetProductsByShipDate with no parameters |
 |
Note that if you'd like to try running the dbo.GetProductsByShipDate stored procedure, you can download it (as well as the other code examples presented here) by clicking the 102592.zip hotlink at the top of the page. If you're running SQL Server 2005 or earlier, you need to change the date data type to datetime.