Subsonic 3 - Passing null value to stored procedure parameter

Question

Using Subsonic 3.0.0.3 is it feasible to pass a null value to a stored procedures parameter? If so, what is the appropriate way?

Details
Say I have an sp where one of the parameters has a default value like:

 CREATE Procedure Test_SPWithNullParams( @p1 int=null, @p2 varchar(50) ) 
  AS
   SELECT 1 as Stuff

Then in my code I want to do something like:

 var db = new Sandbox.DB();  
 StoredProcedure sproc = db.Test_SPWithNullParams( null , "test");
 //alternately --> db.Test_SPWithNullParams("test");

The way the corresponding function generated in StoredProcedures.cs, however, the parameter for @p1 is not nullable. So what are my alternatives? I came across this article (http://brianmrush.wordpress.com/2010/01/19/subsonic-t4-templates-stored-procedures-nullable-parameters) but it seems like a cumbersome work around that effectively branches the code base.

Alternatively I've thought about manually overriding the command object. Something like:

int dummyInt = -1;
StoredProcedure sproc = db.Test_SPWithNullParams( dummyInt , "test");
sproc.Command.Parameters[0].ParameterValue = DBNull.Value;

Thoughts?

Answer 1

I don't have Subsonic at hand, but perhaps you could use nullable int.

int? dummyInt = null;
StoredProcedure sproc = db.Test_SPWithNullParams( dummyInt , "test");

Answer 2

No way. Stored procedures are generated only with no nullable types. Why not try something like this?:

StoredProcedure sproc = db.Test_SPWithNullParams( someNullableInt.HasValue ? someNullableInt.Value: 0, "test");