Stored Procedures that return scalar results

Okay so, let us start with our favorite northwind database. Assume that I wish to write a stored procedure that returns me the number of customers. I could write this procedure as follows –

Create Procedure GetCustomerCount

AS

Select Count(*) from Customers

If you were to run SqlMetal on the above, the extracted stored procedure definition in the strongly typed DataContext would look like as below –

[StoredProcedure(Name="GetCustomerCount")]

public StoredProcedureResult<GetCustomerCountResult> GetCustomerCount()

{

  return this.ExecuteStoredProcedure<GetCustomerCountResult>(

    ((MethodInfo)(MethodInfo.GetCurrentMethod())));

}

Indeed, the above, even though it is a scalar value, the runtime interprets it as a “resultset”. To truly return a scalar, your stored procedure should be written as –

Create Procedure GetCustomerCount

AS

Declare @Count INT

Select @Count = Count(*) from Customers

Return @Count

When you do so, the extracted stored procedure definition in the strongly typed DataContext now looks like this –

[StoredProcedure(Name="GetCustomerCount")]

public int GetCustomerCount()

{

   StoredProcedureResult result =

     this.ExecuteStoredProcedure(

      ((MethodInfo)(MethodInfo.GetCurrentMethod())));

   return result.ReturnValue.Value;

}

Okay, that seems to make a bit more sense J. Truly, the second implementation is really what can be construed as a stored procedure that returns a scalar value. The first implementation will be discussed along with simple resultsets when I get around to blogging about that.

Now once the definition of your stored procedure is set up, using it is brain dead.

Northwind db = new Northwind() ;

Console.WriteLine("Number of Customers is:" + db.GetCustomerCount()) ;

Now that was easy J.