This documentation is archived and is not being maintained.

How to: Use Stored Procedures that Take Parameters (LINQ to SQL)

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: Use Stored Procedures to Return Rowsets (LINQ to SQL).

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: