Default parameter values/optional parameters for .NET stored procedures in SQL Server 2005

Question

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

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

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:

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

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.

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.

Answer 1

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

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

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.