Creating User-Defined Functions

Creating User-Defined Functions You can create your own user-defined functions in SQL Server. For example, you might want to create your own function to computer
Nội dung trích xuất từ tài liệu:
Creating User-Defined FunctionsCreating User-Defined FunctionsYou can create your own user-defined functions in SQL Server. For example, you mightwant to create your own function to compute the discounted price given the original priceand factor to multiply that price by. You create a function using the CREATEFUNCTION statement. There are three types of userdefined functions: • Scalar functions Scalar functions return a single value. The returned value can be of any data type except text, ntext, image, cursor, table, timestamp, and user- defined data types. • Inline table-valued functions Inline table-valued functions return an object of the table type. You can think of a table as a regular database table, except it is stored in memory. An inline table-valued function can return the results retrieved by only a single SELECT statement. • Multistatement table-valued functions Multistatement table-valued functions return an object of the table type. Unlike an inline table-valued function, a multistatement table-valued function can contain multiple T-SQL statements.Youll see examples of these three types of functions in the following sections.Using Scalar FunctionsScalar functions return a single value. Listing 4.2 shows the DiscountPrice.sql script thatcreates the DiscountPrice() function, which returns the original price of an itemmultiplied by a discount factor. These values are passed as parameters to theDiscountPrice() function. You can load this file into Query Analyzer and run it.Listing 4.2: DISCOUNTPRICE.SQL/* DiscountPrice.sql creates a scalar function to return the new price of an item given the original price and a discount factor*/CREATE FUNCTION DiscountPrice(@OriginalPrice money, @Discount float)RETURNS moneyASBEGIN RETURN @OriginalPrice * @DiscountENDThe parameters to the function are placed in brackets after the name of the function in theCREATE FUNCTION statement.Warning Make sure you select the Northwind database from the drop-down list box on the Query Analyzer toolbar before running the script. That way, the function is created in the Northwind database.You can also create functions using Enterprise Manager. You do this by clicking the rightmouse button on the User Defined Functions node in the Databases folder and selectingNew User Defined Function. You can then cut and paste the contents ofDiscountPrice.sql into the Enterprise Manager properties dialog box, as shown in Figure4.4.Figure 4.4: Using Enterprise Manager to define a functionYou can view and modify a function by double-clicking the function name in EnterpriseManager. You can also delete a function using Enterprise Manager. The Object Browserof Query Analyzer allows you to view, modify, and delete functions as well.Tip You can also delete a function using the DROP FUNCTION statement, and you can modify a function using the ALTER FUNCTION statement.Once youve created the function, you can call it. When calling a scalar function, you usethe following syntax:owner.functionNameWhere owner is the database user who owns the function, and functionName is the nameof the function.Lets say you created the DiscountPrice() function using the dbo user, then you call thatfunction using dbo.DiscountPrice(). The following example returns 3.0000, which is 10 *0.3:SELECT dbo.DiscountPrice(10, 0.3);As with any other function, you can pass a column to DiscountPrice(). The followingexample returns 5.4000 and 18.0000; 5.4000 is 18.0000 * 0.3:SELECT dbo.DiscountPrice(UnitPrice, 0.3), UnitPriceFROM ProductsWHERE ProductID = 1;You can of course also pass variables as parameters to a function. As before, thisexample returns 5.4000 and 18.0000:DECLARE @MyDiscountFactor floatSET @MyDiscountFactor = 0.3SELECT dbo.DiscountPrice(UnitPrice, @MyDiscountFactor), UnitPriceFROM ProductsWHERE ProductID = 1;Using Inline Table-Valued FunctionsAn inline table-valued function returns an object of the table type, which is populatedusing a single SELECT statement. Unlike a scalar function, an inline table-valuedfunction doesnt contain a body of statements placed within BEGIN and END statements.Instead, only a single SELECT statement is placed within the function.For example, Listing 4.3 shows the ProductsToBeReordered.sql script that creates theProductsToBeReordered() function. This function returns a table containing the rowsfrom the Products table with a UnitsInStock column value less than or equal to thereorder level parameter passed to the function.Listing 4.3: PRODUCTSTOBEREORDERED.SQL/* ProductsToBeReordered.sql creates an inline table-valued function to return the rows from the Products table whose UnitsInStock column is less than or equal to the reorder level passed as a parameter to the function*/CREATE FUNCTION ProductsToBeReordered(@Reo ...