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 4.2 shows the DiscountPrice.sql 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.

Listing 4.2: DISCOUNTPRICE.SQL

start example
 /*   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 money AS BEGIN   RETURN @OriginalPrice * @Discount END 
end example

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 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 right mouse button on the User Defined Functions node in the Databases folder and selecting New User Defined Function. You can then cut and paste the contents of DiscountPrice.sql into the Enterprise Manager properties dialog box, as shown in Figure 4.4.

click to expand
Figure 4.4: Using Enterprise Manager to define a function

You can view and modify a function by double-clicking the function name in Enterprise Manager. You can also delete a function using Enterprise Manager. The Object Browser of 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 you've created the function, you can call it. When calling a scalar function, you use the following syntax:

 owner.functionName 

Where owner is the database user who owns the function, and functionName is the name of the function.

Let's say you created the DiscountPrice() function using the dbo user, then you call that function 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 following example returns 5.4000 and 18.0000; 5.4000 is 18.0000 * 0.3:

 SELECT dbo.DiscountPrice(UnitPrice, 0.3), UnitPrice FROM Products WHERE ProductID = 1; 

You can of course also pass variables as parameters to a function. As before, this example returns 5.4000 and 18.0000:

 DECLARE @MyDiscountFactor float SET @MyDiscountFactor = 0.3 SELECT dbo.DiscountPrice(UnitPrice, @MyDiscountFactor), UnitPrice FROM Products WHERE ProductID = 1; 

Using Inline Table-Valued Functions

An inline table-valued function returns an object of the table type, which is populated using a single SELECT statement. Unlike a scalar function, an inline table-valued function doesn't 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 the ProductsToBeReordered() function. This function returns a table containing the rows from the Products table with a UnitsInStock column value less than or equal to the reorder level parameter passed to the function.

Listing 4.3: PRODUCTSTOBEREORDERED.SQL

start example
 /*   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(@ReorderLevel int) RETURNS table AS RETURN (   SELECT *   FROM Products   WHERE UnitsInStock <= @ReorderLevel ) 
end example

Unlike a scalar function, you don't have to add the owner when calling an inline table-valued function. You use a SELECT statement to read the table returned by the function as you would any other table. For example, the following SELECT statement displays all the rows and columns returned by the function call ProductsToBeReordered(10):

 SELECT * FROM ProductsToBeReordered(10); 

You can of course also display only selected columns and rows from the table returned by an inline table-valued function. For example:

 SELECT ProductID, ProductName, UnitsInStock FROM ProductsToBeReordered(10) WHERE ProductID <= 50; 

Figure 4.5 shows the results of this SELECT statement.


Figure 4.5: Using an inline table-valued function

Using 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, and allow you to build complex functions.

For example, Listing 4.4 shows the ProductsToBeReordered2.sql script that creates the ProductsToBeReordered2() function. This function returns a table containing the ProductID, ProductName, and UnitsInStock columns from the Products table with a UnitsInStock column value less than or equal to the reorder level parameter. In addition, a new column named Reorder is added to the table, which contains the word Yes or No, depending on whether the product must be reordered.

Listing 4.4: PRODUCTSTOBEREORDERED2.SQL

start example
 /*   ProductsToBeReordered2.sql creates an inline table-valued   function that returns 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 ProductsToBeReordered2(@ReorderLevel int) RETURNS @MyProducts table (   ProductID int,   ProductName nvarchar(40),   UnitsInStock smallint,   Reorder nvarchar(3) ) AS BEGIN   -- retrieve rows from the Products table and   -- insert them into the MyProducts table,   -- setting the Reorder column to 'No'   INSERT INTO @MyProducts     SELECT ProductID, ProductName, UnitsInStock, 'No'     FROM Products;   -- update the MyProducts table, setting the   -- Reorder column to 'Yes' when the UnitsInStock   -- column is less than or equal to @ReorderLevel   UPDATE @MyProducts   SET Reorder = 'Yes'   WHERE UnitsInStock <= @ReorderLevel   RETURN END 
end example

As with an inline table-valued function, you don't have to add the owner when calling an inline table-valued function. You use a SELECT statement to read the table returned by the function as you would any other regular database table. For example, the following SELECT statement displays all the rows and columns returned by the function call ProductsToBeReordered2(20):

 SELECT * FROM ProductsToBeReordered2(20); 

Figure 4.6 shows the results of this SELECT statement.

click to expand
Figure 4.6: Using a multistatement table-valued function

In the next section, you'll learn how to use stored procedures.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net