Calling User-Defined Functions (UDFs)

The code that calls user-defined functions in your database is a little different than the code that calls stored procedures. For example, note the following SQL Server function:

CREATE FUNCTION GetOrderTotal(@OrderID
int)
RETURNS money AS
BEGIN
RETURN
   (SELECT CAST(
   SUM(UnitPrice * Quantity *
   (1 - Discount)) AS money)
   FROM [Order Details]
   WHERE OrderID = @OrderID)
END

You need to call this function as follows.

In C#:

public decimal GetOrderTotal(int orderID)
{
	IDbDataParameter param = this.CreateParameter("@OrderID", orderID);
	IDbDataParameter ReturnValue = this.CreateParameter("@Total", null);
	ReturnValue.Direction = ParameterDirection.ReturnValue;
 
	this.ExecSprocScalar("dbo.GetOrderTotal", param, ReturnValue);
	return (decimal)ReturnValue.Value;
}

And in VB .NET:

Public Function GetOrderTotal(orderID As Integer) As Decimal
	Dim param As IDbDataParameter = Me.CreateParameter("@OrderID", orderID)
	Dim ReturnValue As IDbDataParameter = Me.CreateParameter("@Total", Nothing)
	ReturnValue.Direction = ParameterDirection.ReturnValue
 
	Me.ExecSprocScalar("dbo.GetOrderTotal", param, ReturnValue)
	Return CDec(ReturnValue.Value)
End Function

Note that ExecSprocScalar can be used to call a UDF and the use of the ReturnValue parameter whose Direction is set to ParameterDirection.ReturnValue.