Procedimiento de Creación de la Norma Oficial Mexicana

You retrieve data from a function by using a SELECT statement. Functions can be used in any of the following:

n A SELECT list n A WHERE clause n An expression

n A CHECK or DEFAULT constraint

n A FROM clause with the CROSS/OUTER APPLY function

How a function is used can have a dramatic impact on the performance of the queries that you execute.

A function in the SELECT list is used to calculate an aggregate or perform a computation on one or more columns of the tables in the FROM clause. A function in the WHERE clause is used to restrict a result set based on the results of the function.

Best PraCtiCes USinG FUnctiOnS in a WHERE cLaUSe

You should not create queries that use a function in the WHERE clause because the function would have to execute for each potential row returned from the results of the FROM clause. If you constructed a SELECT statement with a join between TableA and TableB that produced 100 rows matching the join, a function in the WHERE clause would be executed 100 times. If the result of the join produced 10,000 rows, the function would be executed 10,000 times. While you would not be reusing code, if you merged the code within the function into your SELECT statement, you would eliminate all the repetitive queries being executed.

Functions can be nested inside each other so long as the return value of an inner function matches the input parameter of the outer function. For example, a common string-parsing routine might contain code as follows: DATALENGTH(POS(CHARINDEX(REPLACE(...)))).

Functions in the CHECK and DEFAULT constraints are used to extend the static computations available. For example, if you want to validate the area code for a phone number against a list of area codes stored within a table, you can use a function to perform the validation that would not typically be possible because a CHECK constraint doesn’t accept a SELECT statement.

exaM tiP

An inline table-valued function behaves like and is interchangeable with a view.

Quick check

1. What are the three types of functions that you can create? 2. What are the required elements of a function?

Quick check answers

1. You can create a scalar function, which returns a single value, an inline table-valued function, which contains a single SELECT statement and is treated the same as a view, and a multi-statement table-valued function, which returns a table.

2. Every function ends with a RETURN statement. Scalar functions include the value to be returned immediately following the RETURN statement. Inline table-valued functions include the SELECT statement for the result set to return immediately following the RETURN statement. Multi-statement table-valued functions just terminate with a RETURN. With the exception of inline table-valued functions, the entire function body is required to be enclosed in a BEGIN. . .END block.

Practice

creating Functions

In this practice, you create and use three different types of functions.

exercise 1 Create a Scalar Function

In this exercise, you create and use a scalar function to return the current inventory of a product.

1. Open a new query window, type and execute the following code to create the function:

CREATE FUNCTION Production.udf_GetProductInventory (@ProductID INT) RETURNS INT

AS

--There are several locations for a product during each stage -- of manufacturing. We only want finished goods.

--A product can also be stored on multiple shelves and bins, so -- we need to sum the quantities.

BEGIN

DECLARE @Inventory INT

SELECT @Inventory = SUM(a.Quantity)

FROM Production.ProductInventory a INNER JOIN Production.Location b ON a.LocationID = b.LocationID

WHERE a.ProductID = @ProductID

AND b.Name IN ('Miscellaneous Storage','Finished Goods Storage') IF (@Inventory IS NULL) BEGIN SET @Inventory = 0 END RETURN @Inventory END GO

2. In the existing query window, type, highlight, and execute the following code to return results from the function:

SELECT Production.udf_GetProductInventory (325) GO

3. In the existing query window, type, highlight, and execute the following code to verify the results:

SELECT SUM(a.Quantity)

FROM Production.ProductInventory a INNER JOIN Production.Location b ON a.LocationID = b.LocationID

WHERE a.ProductID = 325

AND b.Name IN ('Miscellaneous Storage','Finished Goods Storage') GO

exercise 2 Create an Inline Table-Valued Function

In this exercise, you create and use an inline table-valued function to return all orders that have not yet shipped.

1. Open a new query window, type, highlight, and execute the following code to create an unshipped order:

UPDATE Sales.SalesOrderHeader SET ShipDate = NULL

WHERE SalesOrderID = 75123 GO

2. In the existing query window, type, highlight, and execute the following code to create the function:

CREATE FUNCTION Sales.GetUnshippedOrders() RETURNS TABLE

AS

RETURN SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, a.DueDate, c.Name, b.OrderQty

FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderDetail b ON a.SalesOrderID = b.SalesOrderID

INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL

GO

3. Test the function. Turn on the display of the actual execution plan to verify that SQL Server did not execute the function but instead substituted the function body into the SELECT statement. In the existing query window, type, highlight, and execute the following code:

SELECT c.FirstName, c.MiddleName, c.LastName

FROM Sales.GetUnshippedOrders() a INNER JOIN Sales.Customer b ON a.CustomerID = b.CustomerID

INNER JOIN Person.Person c ON b.PersonID = c.BusinessEntityID GO

4. In the existing query window, type, highlight, and execute the following code to reset the data to the original values:

UPDATE Sales.SalesOrderHeader SET ShipDate = '08/07/2004' WHERE SalesOrderID = 75123 GO

exercise 3 Create a Multi-Statement Table-Valued Function

In this exercise, you create and use a multi-statement table-valued function to return the most recent order for a customer.

1. Open a new query window, type, highlight, and execute the following code to create the function:

CREATE FUNCTION Sales.GetLastShippedCustomerOrder (@CustomerID INT) RETURNS @CustomerOrder TABLE

(SalesOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, DueDate DATETIME NOT NULL, Name NVARCHAR(50) NOT NULL, OrderQty INT NOT NULL) AS

BEGIN

DECLARE @MaxOrderDate DATETIME SELECT @MaxOrderDate = MAX(OrderDate) FROM Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID INSERT @CustomerOrder

SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, a.DueDate, c.Name, b.OrderQty

FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderDetail b ON a.SalesOrderID = b.SalesOrderID

INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxOrderDate

AND a.CustomerID = @CustomerID RETURN

END GO

2. In the existing query window, type, highlight, and execute the following code to test the function:

SELECT * FROM Sales.GetLastShippedCustomerOrder(11000) GO

SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 11000 GO

Lesson Summary

n You can create scalar functions, inline table-valued functions, and multi-statement table-valued functions.

n With the exception of inline table-valued functions, the function body must be enclosed within a BEGIN. . .END block.

n All functions must terminate with a RETURN statement.