SQL Server 2005中.NET存储过程的缺省参数值/可选参数

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:

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

[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());
    }
}

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

这些命令按预期执行,分别打印“1”和“NULL值传递”:

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

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

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

exec dbo.TestProc

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":

我找不到一种方法来为.NET代码中的参数提供默认值。从Googling所能找到的,.NET 4.0将支持可选参数,因此可能是.NET 2.0没有。并且(天真地)更改这样的参数声明会给出错误“不允许使用默认参数说明符”:

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);
}

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.

这样编译干净,但无法部署:VS显示错误“不支持重载的方法,属性或字段”。所以在这一点上我被困住了。

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.

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

1 个解决方案

#1

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

正如您所指出的,这是因为C#2.0不支持可选参数。

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

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

For example:

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.

它有点难看,但可能会让你暂时离开。

#1