Недавно я наткнулся на следующую проблему:
Присвоение null
свойству Value SqlParameter, по-видимому, приводит к исключению параметра из запроса вместо передачи NULL
. Это приводит к потенциально вводящему в заблуждение сообщению об ошибке, когда хранимая процедура не имеет значения по умолчанию для параметра.
У меня есть таблица базы данных со столбцом, который принимает 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
Таблица заполняется с помощью хранимой процедуры. Обратите внимание, что параметр, предназначенный для столбца, не указывает значение по умолчанию.
USE [TheDatabase]
GO
CREATE PROCEDURE TheStoredProcedure
@TheValue varchar(50)
AS
BEGIN
INSERT INTO TheTable (TheValue) VALUES (@TheValue);
END
GO
Выполнение хранимой процедуры со значением и с NULL
работает нормально:
USE [TheDatabase]
GO
EXEC [dbo].[TheStoredProcedure] @TheValue = 'A string for Algernon'
GO
EXEC [dbo].[TheStoredProcedure] @TheValue = NULL
GO
Я запускаю следующий код:
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);
}
}
Первые два вызова метода выполняются нормально: первый добавляет строку в таблицу, второй добавляет NULL
, но вызов ExecuteStoredProcedure()
с null
приводит к следующей ошибке:
System.Data.SqlClient.SqlException (0x80131904): процедура или функция «TheStoredProcedure» ожидает параметр «@TheValue», который не был предоставлен.
Из документации следует, что вы должны использовать null
, если хотите использовать значение параметра по умолчанию в хранимой процедуре.
Этому свойству можно присвоить значение null или Value. Используйте значение, чтобы отправить значение NULL в качестве значения параметра. Используйте null или не устанавливайте Value, чтобы использовать значение по умолчанию для параметра.
Поскольку мой параметр не имеет значения по умолчанию, следует ожидать какой-то ошибки. Однако то, что мы получаем, кажется вводящим в заблуждение: ошибка, указывающая на то, что параметр не был получен на стороне базы данных, в отличие от ошибки, указывающей на неожиданное значение (или, скорее, на отсутствие значения) этого параметра.
Или это на самом деле ожидаемое поведение, когда установка значения null
эффективно исключает передачу параметра на сервер при выполнении команды?
Обновление от 20 января 2010 г.
С тех пор документация была обновлена:
Для этого свойства можно установить значение
null
илиDBNull.Value
. ИспользуйтеDBNull.Value
, чтобы отправить значение NULL в качестве значения параметра. Используйтеnull
или не устанавливайте Значение, чтобы использовать для параметра значение по умолчанию.
Возможно, это не кристально ясно, но, по крайней мере, недвусмысленно.
Ключевым здесь является то, что значение параметра по умолчанию, определенное для хранимой процедуры, вступает в игру, если объект SqlParamer
имеет null
для свойства Value, и я могу только предположить, что это достигается «за кулисами», опуская его из выполнение запроса.
Я предполагаю, что урок, который нужно усвоить здесь, заключается в том, чтобы всегда определять ваши хранимые процедуры со значениями по умолчанию для ваших необязательных параметров.
-S
@TheValue varchar(50) = NULL
в своей хранимой процедуре, чтобы она работала со значением по умолчанию на стороне БД. - person shahkalpeshp   schedule 07.12.2018null
- person Panagiotis Kanavos   schedule 07.12.2018null
(c#), это не будет рассматриваться какDBNull.Value
. Как вы думаете, что ищет ОП и как вы этого добиваетесь? - person shahkalpeshp   schedule 07.12.2018DbNull.Value
. Нет, почему ты думаешь об этом. Это то, что говорят документы, даже если они, кажется, имеют опечатку - person Panagiotis Kanavos   schedule 07.12.2018DBNull.Value
, который заботится. Я не уверен, как передать нулевое значение (С#), кроме использованияDBNull.Value
? - person shahkalpeshp   schedule 07.12.2018