SQL Server 2005 中 .NET 存储过程的默认参数值/可选参数 [英] Default parameter values/optional parameters for .NET stored procedures in SQL Server 2005

问题描述

SQL Server 是一个在 .NET 2.0 框架上用 C# 编写的存储过程，它有一个 SqlInt32 参数.我试图使参数可选.这是一个最小的测试用例，它只打印传递给它的整数:

SQL Server a stored procedure written in C# on the .NET 2.0 framework that has a SqlInt32 parameter. I am trying to make the parameter optional. Here is a minimal test case that just prints the integer passed to it:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestProc(
    SqlInt32 TestInt
    )
{
    SqlPipe pipe;
    pipe = SqlContext.Pipe;

    if (TestInt.IsNull)
    {
        pipe.Send("NULL value passed");
    }
    else
    {
        pipe.Send(TestInt.ToString());
    }
}

这些命令按预期执行，并分别打印1"和传递的NULL值":

These commands execute as expected, and print "1" and "NULL value passed", respectively:

exec dbo.TestProc @TestInt = 1
exec dbo.TestProc @TestInt = null

但是，我的目标是为@TestInt 分配一个默认值 NULL，这将允许我只执行以下命令:

However, my goal is to assign a default of NULL to @TestInt, which would allow me to execute just this command:

exec dbo.TestProc

我找不到为 .NET 代码中的参数提供默认值的方法.从我通过谷歌搜索可以找到的信息来看，.NET 4.0 将支持可选参数，所以大概 .NET 2.0 不支持.并且(天真地)像这样更改参数声明会产生错误不允许使用默认参数说明符":

I can't find a way to provide a default value to the parameter within the .NET code. From what I could find by Googling, .NET 4.0 will support optional parameters, so presumably .NET 2.0 does not. And (naively) changing the parameter declaration like this gives the error "default parameter specifiers are not allowed":

SqlInt32 TestInt = SqlInt32.Null

我还尝试通过添加以下代码重载该方法:

I also tried overloading the method by adding this code:

public static void TestProc()
{
    SqlInt32 intNull;
    intNull = SqlInt32.Null;
    TestProc(intNull);
}

此编译干净，但无法部署:VS 显示错误不支持重载的方法、属性或字段".所以在这一点上我被卡住了.

This compiles cleanly, but cannot be deployed: VS shows the error "Overloaded methods, properties or fields are not supported". So at this point I'm stuck.

真正的用例当然更复杂:它是一个 TSQL 日志模块，它调用存储过程来处理日志消息.处理过程是在运行时动态识别的，调用代码不知道它是在调用 TSQL 还是 .NET 过程.这要求所有程序都支持相同的参数，其中有几个是可选的.调用代码已经在生产中，所以我试图避免更改它以在每次调用时传递每个参数.在 TSQL 过程中，这不是问题，因为可选参数很容易，但在 .NET 中显然不是.

The real use case is of course more complex: it's a TSQL logging module that calls stored procedures to handle log messages. The handling procedure is identified dynamically at runtime, and the calling code doesn't know if it's calling a TSQL or .NET proc. That requires all procedures to support the same parameters, and several are optional. The calling code is already in production, so I'm trying to avoid changing it to pass every parameter on every call. In TSQL procs, it's not an issue because optional parameters are easy, but apparently not in .NET.

推荐答案

正如你所指出的，这是因为 C#2.0 不支持可选参数.

As you pointed out, this is because C#2.0 doesn't support optional parameters.

一种解决方法可能是将 .NET 存储过程包装在接受默认参数的常规 T-SQL 存储过程中.

One workaround may be to wrap the .NET stored procedures in regular T-SQL stored procedures that do accept default paramters.

例如:

CREATE PROCEDURE TestProcWrapper
(
    @TestIntWrapperParam int = null
)
AS
EXEC TestProc @TestInt = @TestIntWrapperParam 

这有点难看，但现在可能会让你上路.

It's a bit ugly but might get you on the way for now.

这篇关于SQL Server 2005 中 .NET 存储过程的默认参数值/可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！