Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
How to: Use Stored Procedures that Take Parameters

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);

Community Additions

ADD
Show:
© 2015 Microsoft