Executing SQL Server User-Defined Scalar Functions

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:2

Thêm vào BST

Báo xấu

144
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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. 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 dbo.ExtendedPrice ( @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: ScalarFunctionForm.cs // Namespaces, variables, and constants using 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; 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.]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 the UnitPrice, Quantity, and Discount values. The result is returned as a result set column named ExtendedPrice. [ Team LiB ]