Calling a stored procedure that has optional parameters defined

One of my stored procedures has an optional parameter defined as follows:

 @optionalParam = null,
 @nonOptionalParam

If I set the command like this:

  var cmd = unitOfWork.Context.DataProviderObjectFactory.CreateCommand();
       cmd.CommandText = "exec dbo.my_stored_procedure @optionalParam, @nonOptionalParam"
       cmd.Parameters.Add("optionalParam ", null);
       cmd.Parameters.Add("nonOptionalParam", 0);

I get an error indicating that optionalParam is required but was not supplied. How do I deal with this?

TIA.

Klaus

The standard ADO.NET approach for calling stored procedures is to set cmd.CommandText to the proc name and then cmd.CommandType to CommandType.StoredProcedure, you can then specify the parameters in the parameters collection.

e.g.

var cmd = unitOfWork.Context.DataProviderObjectFactory.CreateCommand();
cmd.CommandText = "dbo.my_stored_procedure";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@optionalParam ", null);
cmd.Parameters.Add("@nonOptionalParam", 0);

Also given the optionalParam is defaulting to null in your proc you wont need to include it in the parameter list.

Jeremy