[ Team LiB ] Recipe Executing SQL Server User-Defined Scalar Functions Problem Your SQL Server 2000 database includes a user-defined function that returns a scalar value. You want to retrieve the value from this function using . | Team LiB Recipe Executing SQL Server User-Defined Scalar Functions Problem Your SQL Server 2000 database includes a user-defined function that returns a scalar value. You want to retrieve the value from this function using . Solution Invoke the function as you would a query or stored procedure. The sample code as shown in Example 2-16 uses a single SQL Server function ExtendedPrice Calculates and returns the extended price for an order line item based on the unit price quantity and discount. Example 2-16. SQL Server function ExtendedPrice CREATE FUNCTION @UnitPrice money @Quantity smallint @Discount real RETURNS money AS BEGIN RETURN @UnitPrice @Quantity 1 - @Discount END The sample code defines a SQL statement that uses the ExtendedPrice user-defined function. The statement is used by a DataAdapter to fill a DataTable with all records from the Order Details table in Northwind together with the extended price calculation for each record. The default view of the table is bound to a data grid on the form. The C code is shown in Example 2-17. Example 2-17. File Namespaces variables and constants using System using using using . . . String sqlText SELECT UnitPrice Quantity Discount ExtendedPrice FROM Order Details Create DataAdapter and fill the table. SqlDataAdapter da new SqlDataAdapter sqlText Sql_ConnectString DataTable dt new DataTable dt Bind the default view for the table to the grid. dt Discussion A user-defined scalar function is a SQL routine that accepts one or more scalar input parameters and returns a single value. A user-defined scalar function is invoked from a query or executed like a stored procedure using an EXECUTE statement. You can invoke scalar functions where scalar expressions can be used. To invoke a scalar function use the following syntax databaseName.
