Executing SQL Server User-Defined Scalar Functions

[ Team LiB ] Recipe 2.13 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 ADO.NET.
Nội dung trích xuất từ tài liệu:
Executing SQL Server User-Defined Scalar Functions[ Team LiB ]Recipe 2.13 Executing SQL Server User-Defined Scalar FunctionsProblemYour SQL Server 2000 database includes a user-defined function that returns a scalarvalue. You want to retrieve the value from this function using ADO.NET.SolutionInvoke 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: ExtendedPriceCREATE FUNCTION dbo.ExtendedPrice( @UnitPrice money, @Quantity smallint, @Discount real)RETURNS moneyASBEGIN RETURN ((@UnitPrice * @Quantity) * (1 - @Discount))ENDThe sample code defines a SQL statement that uses the ExtendedPrice user-definedfunction. The statement is used by a DataAdapter to fill a DataTable with all records fromthe Order Details table in Northwind together with the extended price calculation for eachrecord. 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: ScalarFunctionForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Data;using System.Data.SqlClient;// . . .String sqlText = SELECT *, + dbo.ExtendedPrice(UnitPrice, Quantity, Discount) ExtendedPrice + FROM [Order Details];// Create DataAdapter and fill the table.SqlDataAdapter da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings[Sql_ConnectString]);DataTable dt = new DataTable( );da.Fill(dt);// Bind the default view for the table to the grid.resultDataGrid.DataSource = dt;DiscussionA user-defined scalar function is a SQL routine that accepts one or more scalar inputparameters and returns a single value. A user-defined scalar function is invoked from aquery or executed like a stored procedure using an EXECUTE statement.You can invoke scalar functions where scalar expressions can be used. To invoke a scalarfunction, use the following syntax:[databaseName.]ownerName.functionName([argument1][, . . . ])In the solution code, the ExtendedPrice function is called as shown by:dbo.ExtendedPrice(UnitPrice, Quantity, Discount)This calculates the extended price for each row in the Order Details table based on theUnitPrice, Quantity, and Discount values. The result is returned as a result set columnnamed ExtendedPrice.[ Team LiB ]