Passing NULL 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.

Forcing the Use of Default Parameter Values

Being able to force the use of default parameter values presents developers with a handy option when designing applications that use ADO.NET to execute stored procedures with optional named parameters. If you want a stored procedure to use the procedure-specified default value, simply don't generate a parameter for the SqlCommand object that's executing the stored procedure. (Be sure to specify the default parameter value in the stored procedure; otherwise, the code will throw an exception.) This means that you can build logic that bypasses the creation of any parameter when you want the server-side query processor to use the specified default value. An example of this approach is shown in Listing 2.

Listing 2: Executing the Stored Procedure with No Parameters

Admittedly, this approach isn't very practical for stored procedures with a lot of optional parameters because your developers will have to come up with a state machine to figure out which Parameter objects to build and which to populate. Of course, that's not that hard to do once you figure out a good strategy.

Dealing with NULL Values in an Application's Logic

Users don't always fill in all the input fields in applications' UIs, which can cause problems. For example, suppose the UI for the application that runs the dbo.GetProductsByShipDate stored parameter has a single input field in which users are supposed to enter the desired shipping date. In this scenario, does a blank field mean that the user simply forgot to fill in the field, or does it mean that the user wants to pass in NULL? Asking users to enter NULL when applicable wouldn't be a viable option because most users don't understand the concept of NULL.

If your application needs to decide at runtime whether a user simply forgot to fill in a field or wants to pass in NULL, a better solution would be creating a UI like that in Figure 2.

Figure 2: UI for dbo.GetProductsByShipDate

When a user enters a date in the Date Shipped input box, the application executes the dbo.GetProductsByShipDate stored parameter, using that date as the input parameter. If the Not Shipped Yet check box is selected, NULL is used as the input parameter.

Regardless of how your UI works, it's up to you to decide how to set the parameter value passed to the query processor. For example, there are a couple of options for the UI in Figure 2:

Listing 3: Setting the Parameter Value to NULL

Listing 4: Bypassing Parameter Creation

• Option 1: Set the parameter value to NULL. Test for a checked state in the Not Shipped Yet check box. If that check box is selected, set the @DateWanted parameter value to DBNull.Value or Nothing in Visual Basic.NET code or null in C# code. Otherwise, use the date in the Date Shipped input box for the @DateWanted parameter value. The code in Listing 3 shows the use of both DBNull.Value and Nothing. Both lines produce the same result.
• Option 2: Bypass the creation of the parameter. Test for a checked state in the Not Shipped Yet check box. If that check box is selected, bypass the creation of the parameter for the SqlCommand object. Otherwise, use the date in the Date Shipped input box for the @DateWanted parameter value. The code in Listing 4 demonstrates this approach. This code depends on having the default parameter value set in the stored procedure definition. Although coupling your UI to the database doesn't follow best practices, this option is easy to understand and manage.

Note that not all of the new SQL Server 2008 data types are exposed in Visual Studio 2008 SP1. Although I could see the parameter value types in an enumerated list, when I tried to use the Type.parse method, only the old types were available. Fortunately, the Visual Studio 2008 SP1 development tools knew how to enumerate the new SqlDbType data types.

Handling the Unknown

There are many situations in which an input parameter value might not be provided. In such cases, you have to be careful when including a value that essentially says "we don't know what the value is." As you've seen here, it's possible to handle NULL parameters in several ways. Given that many stored procedures have dozens of input parameters, it's handy to know how you can invoke them without having to set a value for each and every parameter.