Share via


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

Example

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
<FunctionAttribute(Name:="dbo.CustOrderTotal")> _
 Public Function CustOrderTotal(<Parameter(Name:="CustomerID", DbType:="NChar(5)")> ByVal customerID As String, <Parameter(Name:="TotalSales", DbType:="Money")> ByRef totalSales As System.Nullable(Of Decimal)) As <Parameter(DbType:="Int")> Integer
    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), customerID, totalSales)
    totalSales = CType(result.GetParameterValue(1), System.Nullable(Of Decimal))
    Return CType(result.ReturnValue, Integer)
End Function
    [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:

Dim db As New Northwnd("C:\...\northwnd.mdf")
Dim totalSales As Decimal? = 0
db.CustOrderTotal("alfki", totalSales)

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

Console.WriteLine(totalSales);

See Also

Reference

Using Nullable Types (C# Programming Guide)

Concepts

Downloading Sample Databases (LINQ to SQL)

Nullable Value Types (Visual Basic)

Other Resources

Stored Procedures (LINQ to SQL)