Как указать «DEFAULT» в качестве значения параметра SQL в ADO.NET?

Параметры SQL-запроса заменяют буквальные значения только.

Вы не можете отправить ключевое слово SQL в качестве значения параметра, так же как вы не можете отправить идентификатор таблицы, идентификатор столбца, список значений (например, для предиката IN) или выражение. Значение параметра всегда интерпретируется как буквальное значение, как если бы вы включили строковый литерал в кавычках или числовой литерал в свой запрос.

Извините, но вы должны включить ключевое слово SQL как часть SQL-запроса перед подготовкой этого запроса.

Насколько я знаю, единственный способ указать SQL Server использовать значение по умолчанию — это использовать ключевое слово DEFAULT или исключить его из списка параметров. Это означает, что ключевое слово DEFAULT должно использоваться в вашем параметризованном операторе SQL. Итак, что-то вроде:

Select ...
From dbo.udf_Foo( DEFAULT, @Param2, @Param3, DEFAULT, .... )

Я полагаю, что другим подходом было бы запросить системные каталоги для фактического значения различных значений DEFAULT и определить, следует ли устанавливать для SqlParameter значение по умолчанию таким образом, но для этого требуется запутанный второй запрос для получения значений по умолчанию.

Если у вас есть следующая функция (например):

CREATE FUNCTION dbo.UFN_SAMPLE_FUNCTION 
(
    @Param1 nvarchar(10), 
    @Param2 int = NULL
)
RETURNS TABLE
AS 
RETURN 
   SELECT @Param1 AS Col1, @Param2 AS Col2;
GO

Затем вы можете использовать его следующим образом (вариант 1):

SELECT * FROM dbo.UFN_SAMPLE_FUNCTION ('ABC', DEFAULT);

что правильно, и вы получите следующий результат:

Col1       Col2
---------- -----------
ABC        NULL

Но если вы попытаетесь использовать параметризованный запрос (вариант 2):

exec sp_executesql N'SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)',N'@P1 nvarchar(10),@P2 int',@P1=N'abc',@P2=default;

вы получите сообщение об ошибке:

Msg 8178, Level 16, State 1, Line 0
The parameterized query '(@P1 nvarchar(10),@P2 int)SELECT * FROM dbo.UFN_SAMPLE_FUNCTION' expects the parameter '@P2', which was not supplied.

Если у вас есть следующий код .net:

public void RunTVF(string param1, int? param2)
{
    using (SqlConnection con = GetProdConection())
    {
        using (var cmd = new SqlCommand("SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)", con))
        {
            cmd.CommandType = CommandType.Text;
            var param = new SqlParameter
            {
                ParameterName = "@P1",
                SqlDbType = SqlDbType.NVarChar,
                Size = 10   ,
                Value = param1
            };
            cmd.Parameters.Add(param);
            param = new SqlParameter
            {
                ParameterName = "@P2",
                SqlDbType = SqlDbType.Int,
                Value = param2
            };
            cmd.Parameters.Add(param);

            cmd.Connection.Open();
            using (IDataReader dataReader = cmd.ExecuteReader())
            {
                //...
            }
        }
    }
}

тогда, в случае, если param2 = null, как предложил Джек выше, сценарий, созданный кодом, будет идентичен варианту 2 и приведет к той же ошибке. Таким образом, вы не можете использовать NULL в этом случае. Вы также не можете установить DEFAULT в качестве значения SQLParameter.

Что вы можете сделать, так это создать хранимую процедуру, чтобы обернуть вызов вашей функции и переместить значение по умолчанию из функции в SP. Пример:

CREATE PROCEDURE dbo.USP_SAMPLE_PROCEDURE
( 
    @Param1 nvarchar(10), 
    @Param2 int = NULL, --DEFAULT value now is here (remove it from the function)
    @Statement nvarchar(max)
)
AS
BEGIN
    SET NOCOUNT ON;
    EXEC sp_executesql @Statement,N'@P1 nvarchar(10),@P2 int',@P1=@Param1,@P2=@Param2;
END

Код .NET будет выглядеть следующим образом:

public void RunWrapper(string param1, int? param2)
{
    using (SqlConnection con = GetProdConection())
    {
        using (var cmd = new SqlCommand("USP_SAMPLE_PROCEDURE", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            var param = new SqlParameter
            {
                ParameterName = "@Param1",
                SqlDbType = SqlDbType.NVarChar,
                Size = 10,
                Value = param1
            };
            cmd.Parameters.Add(param);
            param = new SqlParameter
            {
                ParameterName = "@Param2",
                SqlDbType = SqlDbType.Int,
                Value = param2
            };
            cmd.Parameters.Add(param);
            param = new SqlParameter
            {
                ParameterName = "@Statement",
                SqlDbType = SqlDbType.NVarChar,
                Size = -1, //-1 used in case you need to specify nvarchar(MAX)
                Value = "SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)"
            };
            cmd.Parameters.Add(param);

            cmd.Connection.Open();
            using (IDataReader dataReader = cmd.ExecuteReader())
            {
                //...
            }
        }
    }
}

В этом случае null в качестве значения для param2 будет преобразовано в правильный DEFAULT, и будет создан следующий скрипт:

exec USP_SAMPLE_PROCEDURE @Param1=N'ABC',@Param2=default,@Statement=N'SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)'

что даст вам следующий результат:

Col1       Col2
---------- -----------
ABC        NULL

Я не уверен, что это лучшая практика. Это всего лишь обходной путь.

Хотя вы не можете установить ключевое слово SQL в качестве значения параметра, в этом случае вы можете пойти и получить ЗНАЧЕНИЕ ПО УМОЛЧАНИЮ.

 SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name'"

если вы передадите нулевое значение точки в качестве значения параметра, оно будет использовать sql DEFAULT, если вы передадите значение DBNull.Value в точке, оно будет использовать sql NULL