Export (0) Print
Expand All

How to: Use Stored Procedures that Take Parameters

LINQ to SQL maps output parameters to reference parameters, and for value types declares the parameter as nullable.

For an example of how to use an input parameter in a query that returns a rowset, see How to: Return Rowsets.

The following example takes a single input parameter (the customer ID) and returns an out parameter (the total sales for that customer).

CREATE PROCEDURE [dbo].[CustOrderTotal] 
@CustomerID nchar(5),
@TotalSales money OUTPUT
AS
SELECT @TotalSales = SUM(OD.UNITPRICE*(1-OD.DISCOUNT) * OD.QUANTITY)
FROM ORDERS O, "ORDER DETAILS" OD
where O.CUSTOMERID = @CustomerID AND O.ORDERID = OD.ORDERID
    [Function(Name="dbo.CustOrderTotal")]
	[return: Parameter(DbType="Int")]
	public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
	{
		IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
		totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
		return ((int)(result.ReturnValue));
	}

You would call this stored procedure as follows:

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
decimal? totalSales = 0;
db.CustOrderTotal("alfki", ref totalSales);

Console.WriteLine(totalSales);
Show:
© 2015 Microsoft