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

SQL查询参数将取代文字值 只有 .

IN 谓词)或表达式。参数的值为 解释为文本值,就像在查询中包含带引号的字符串文本或数字文本一样。

之前 你准备好这个问题。

另外,告诉SQLServer使用默认值的唯一方法是通过 DEFAULT 违约 关键字必须在参数化SQL语句中。比如说:

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

违约

如果您具有以下功能(例如):

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

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.

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

无效的 在这种情况下,您不能设置 违约 作为SQLParameter的值。

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

在这种情况下 无效的 参数2 将被翻译成正确的 将生成以下脚本:

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

将得到以下结果:

列1列2
ABC空

我不确定这是否是最佳做法。这只是工作。

虽然不能将SQL关键字设置为参数的值,但在本例中,可以获取默认值。

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

如果您传递一个dotnetnull值作为参数值,它将使用sql默认值 如果您传递一个dot net DBNull.Value,它将使用sql NULL