I stumbled across the following issue recently:
Assigning a null to the Value property of an SqlParameter appears to cause the parameter to be omitted from the query rather than passing NULL. This results in a potentially misleading error message when the Stored Procedure has no default value for the parameter.
I have a database table with a column that accepts NULL.
USE [TheDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TheTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TheValue] [varchar](50) NULL,
CONSTRAINT [PK_TheTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The table is populated using a Stored Procedure. Notice that the parameter targeting the column does not specify a default value.
USE [TheDatabase]
GO
CREATE PROCEDURE TheStoredProcedure
@TheValue varchar(50)
AS
BEGIN
INSERT INTO TheTable (TheValue) VALUES (@TheValue);
END
GO
Executing the Stored Procedure with a value and with a NULLboth work fine:
USE [TheDatabase]
GO
EXEC [dbo].[TheStoredProcedure] @TheValue = 'A string for Algernon'
GO
EXEC [dbo].[TheStoredProcedure] @TheValue = NULL
GO
I run the following code:
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
ExecuteStoredProcedure("A string for Algernon");
ExecuteStoredProcedure(DBNull.Value);
ExecuteStoredProcedure(null);
Console.ReadKey();
}
private static void ExecuteStoredProcedure(object value)
{
using (var connection = new SqlConnection("Server=TheServer;Database=TheDatabase;Persist Security Info=False;Integrated Security=true;"))
{
connection.Open();
using (var sqlCommand = new SqlCommand("TheStoredProcedure", connection) {CommandType = CommandType.StoredProcedure})
{
sqlCommand.Parameters.Add(new SqlParameter("@TheValue", SqlDbType.VarChar, 50) {Value = value});
try
{
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
Console.WriteLine(DateTime.Now);
}
}
The first two method calls run fine: The first adds the string to the table, the second adds a NULL, but calling ExecuteStoredProcedure() with a null results in the following error:
System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'TheStoredProcedure' expects parameter '@TheValue', which was not supplied.
From the documentation it appears that you should use null when you wish to use the default value of parameter in the Stored Procedure.
This property can be set to null or Value. Use Value to send a NULL value as the value of the parameter. Use null or do not set Value to use the default value for the parameter.
Since my parameter does not have a default value, some sort of error is to be expected. However, what we get seems misleading: an error indicating that the parameter was not received on the database side, as opposed to an error indicating an unexpected value (or rather: lack of value) on that parameter.
Or is this in fact expected behavior, that setting Value to null effectively excludes the parameter from being passed to the server on execution of the command?
Update 20.01.2010
The documentation has since been updated:
This property can be set to
nullorDBNull.Value. UseDBNull.Valueto send a NULL value as the value of the parameter. Usenullor do not set Value to use the default value for the parameter.
It is perhaps not crystal clear, but it is at least unambiguous.
Key here is that the default value of the parameter as defined for the Stored Procedure comes into play if the SqlParamer object has a null for its Value property, and I can only assume that this is achieved "behind the scenes" by omitting it from the query execution.
I guess the lesson to learn here is to always define your Stored Procedures with default values on your optional parameters.
-S
