在ADO.NET中如何将SQL参数值指定为“DEFAULT”？

Question

我有一个针对SQL2005的参数化SQL查询，该查询在代码中动态创建，因此我使用了ADO.NET的SqlParameter类来向SqlCommand添加SQL参数。

在上述的SQL查询中，我从一个带有默认值的表值函数中选择。我希望我的动态sql有时为这些默认参数指定一个值，而有时则希望指定SQL DEFAULT-如表值函数中所定义-应该被使用。

为了保持代码的简洁，当使用非默认值时，我不想动态添加SQL DEFAULT关键字并将其参数化，我只想将DEFAULT设置为我的SQLParameter的值。

我可以这样做吗？在这种情况下，最佳做法是什么？

- Llyle

---

SQL中的“DEFAULT”关键字是什么？有默认约束，允许您在未为列提供默认值的情况下设置默认值... - OMG Ponies

@OMG Ponies：你可以在INSERT（或UPDATE）语句中像使用NULL一样使用DEFAULT：INSERT INTO foo(col1, col2) VALUES(1234, DEFAULT)。这是标准的SQL语法，但鲜为人知。 - Bill Karwin

@Bill Karwin：谢谢，这对我来说确实是个好消息。 - OMG Ponies

甚至可以将“DEFAULT”传递给TVF。然而，SQL似乎不喜欢在传递给TVF的函数中使用“DEFAULT”（即Coalesce(@Foo, DEFAULT)）。 - Thomas

这可能会引起您的兴趣：https://dev59.com/HnE95IYBdhLWcg3wE56C - DanMan

Answer 1

SQL查询参数仅用于替换字面值。

您不能将SQL关键字作为参数值发送，就像您不能发送表标识符、列标识符、值列表（例如用于IN谓词），或表达式一样。 参数的值始终被解释为字面值，就好像您在查询中包含引号括起来的字符串字面量或数字字面量。

抱歉，但您必须在准备查询之前将SQL关键字作为SQL查询的一部分包含在内。

- Bill Karwin

---

是的，这有点糟糕。我理解为什么会这样，但对我来说有些棘手 :) - Llyle

在存储过程中，如果你将SqlParameter.Value设为null，它会自动将默认值发送到SQL Server。如果你想将数据库字段设置为null，请使用 DBNull.Value。 - Jalal

Answer 2

据我所知，唯一告诉SQL Server使用默认值的方法是通过使用DEFAULT关键字或从参数列表中排除它。这意味着使用DEFAULT关键字必须在您的参数化SQL语句中。因此，类似以下的语句：

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

我认为另一种方法是查询系统目录以获取各种DEFAULT值的实际值，并确定是否通过这种方式将SqlParameter设置为默认值，但这需要进行复杂的第二个查询以获取默认值。

- Thomas

Answer 3

如果您有以下函数（例如）：

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())
            {
                //...
            }
        }
    }
}

接下来，如果像Jack建议的那样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())
            {
                //...
            }
        }
    }
}

在这种情况下，param2 的值为 null 将被翻译为正确的 DEFAULT，并将生成以下脚本：

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

这将会给你以下结果：

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

我不确定这是否是最佳实践，这只是一种解决方法。

- Alex

---

这个问题在于它假设 [null] 意味着 [default]，而这并不总是正确的。这个问题是微软实现中的一个真正的缺陷。 - tbone

Answer 4

在这种情况下，虽然您不能将SQL关键字设置为参数的值，但可以获取默认值。

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

- Chris Catignani

---

默认值可以是SQL表达式，包括调用函数。因此，在.NET方面，这通常没有什么用处。 - MgSam

@MgSam 不完全是这样...这会从系统目录中获取默认值...然后可以将其作为参数传递...不太优雅，但坚持遵循OP的原始问题。或者我们在谈论不同的事情吗？ - Chris Catignani

Answer 5

如果您将一个 .NET 的 null 值作为参数传递，则会使用 SQL 默认值，如果您传递的是 .NET 的 DBNull.Value，则会使用 SQL NULL。

- Jack

---

-1 这是不正确的。在上下文中调用存储过程时，SqlCommand.CommandType = CommandType.Text，您可以通过不定义参数、不设置参数或将其设置为 null（例如 SqlParameter.Value = null;）来指示具有默认值的参数实际上应该使用该默认值。但这与使用 T-SQL 的 DEFAULT 不同（这就是这个问题所涉及的）。这与不带参数调用存储过程相同。而这个问题是关于 TVFs 的，而不是存储过程，TVFs 不允许不指定所有输入参数。 - Solomon Rutzky

你知道是否可以通过参数将DEFAULT传递给TVF调用吗？我的研究表明不行，必须检测未指定值并手动在SqlText中替换@ParameterName为DEFAULT。这是我目前的看法。 - tbone

@srutzky 这是发给你的。 - tbone