Some tasks are just too complicated for dynamic query building and/or require a greater level of control. To handle this, SubSonic supports stored procedures. Each stored procedure will produce an equivalent static method in the class defined in the configuration file. By default this is SPs. Each method will have one parameter for each stored procedure parameter and return a StoredProcedure object.

SubSonic.StoredProcedure sp = SPs.CustOrderHist(customerID);

If you have created new stored procedures, read my post on how to:
Add New Stored Procedures to dashCommerce

Now that SubSonic recognizes your custom stored procedures, the next question is how to access/get their return value?

To get the return value of this stored procedure with subsonic we need to add a return parameter to the stored procedure. To do this, we use the AddReturnParameter method of the stored procedure’s command object.

ex. sp.command.AddReturnParameter();

The following code adds a return parameter, executes the stored procedure and assigns the return value to a variable.

Subsonic.StoredProcedure sp = MettleSystems.dashCommerce.Store.SPs.EmailExists(txtEmail.Text);
// There is also a MettleSystems.dashCommerce.Core.SPs in dashCommerce
// SPs is an ambiguous reference
sp.Command.AddReturnParameter();
sp.Execute();
string spResult = sp.Command.Parameters.Find(delegate(QueryParameter qp) {
return qp.Mode == ParameterDirection.ReturnValue;
}).ParameterValue.ToString();

byte result = Convert.ToByte(spResult);

In the above code I used the EmailExists function generated by SubSonic to execute the stored procedure, dashCommerce_Store_EmailExists, which returns a byte.

Thanks to:  Mayank’s Blog