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 | 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 compute the discounted price given the original price and factor to multiply that price by. You create a function using the CREATE FUNCTION 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. You ll see examples of these three types of functions in the following sections. Using Scalar Functions Scalar functions return a single value. Listing shows the script that creates the DiscountPrice function which returns the original price of an item multiplied by a discount factor. These values are passed as parameters to the DiscountPrice function. You can load this file into Query Analyzer and run it. 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 money AS BEGIN RETURN @OriginalPrice @Discount END The parameters to the function are placed in brackets after the name of the function in the CREATE FUNCTION statement. Warning Make sure you select the Northwind database from the drop-down list box on
