How to declare a nullable guid as an optional parameter for a C# CLR stored procedure with SQL Server

Question

I'm writing a C# stored procedure which is deployed on SQL Server 2008 R2 (so .Net 3.5) and want to declare an optional parameter as a nullable guid. Here's what I tried first:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void spCalcPerc(
     SqlGuid pID
    , SqlGuid sID = DBNull.Value
    )

This failed with the compile time error:

Default parameter value for 'sID' must be a compile-time constant

which is because DBNull.Value is not a constant, which is a pain.

So I tried changing the declaration to:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void spCalcPerc(
     SqlGuid pID
    , Guid? sID = null
    )

This compiles but now I get this deployment error:

Deploy error : Could not create a type for parameter System.Nullable

Trying:

, SqlGuid sID = null

Gives this compile time error:

A value of type '< null >' cannot be used as a default parameter because there are no standard conversions to type 'System.Data.SqlTypes.SqlGuid'

So feeling rather stuck I resorted to this:

, SqlGuid sID = "00000000-0000-0000-0000-000000000000"

Which I didn't want to do as testing for that string in the code feels like a kludge. However that doesn't work either as I get this compile error:

A value of type 'string' cannot be used as a default parameter because there are no standard conversions to type 'System.Guid'

Gah, 4 different approaches and none of them work. sigh

Would appreciate your thoughts or a push in the right direction please.

Answer 1

Try

, SqlGuid sID = New Guid()

You can use

if (sID == Guid.Empty)

To check if it is has not been assigned a value.