Los datos jerárquicos, tales como una estructura de organización, son un ejemplo de los datos que no se pueden recopilar en una sola interrogación. La tabla Employees de la base de datos de Northwind Company contiene un campo llamado ReportsTo que contiene el EmployeeID del empleado. GetManagerReports es una funcion de tabla multi sentencias: que devuelve una lista de los empleados que apuntan a un empleado específico, directamente o indirectamente.

CREATE FUNCTION dbo.GetManagerReports ( @iEmployeeID int )
RETURNS @ManagerReports TABLE
   (
   EmployeeID int,
   EmployeeFirstName     nvarchar(10),
   EmployeeLastName nvarchar(20),
Title nvarchar(30),
TitleOfCourtesy nvarchar(25),
Extension nvarchar(4),
   ManagerID int
   )
AS
BEGIN

DECLARE

@iRowsAdded int, -- Counts rows added to
-- table with each iteration
@PREPROCESSED tinyint, -- Constant
for record prior
-- to processing
@PROCESSING tinyint, -- Constant
for record
-- being processed
@POSTPROCESSED tinyint -- Constant for
records that
-- have been processed

SET @PREPROCESSED = 0
SET @PROCESSING = 1
SET @POSTPROCESSED = 2

DECLARE @tblReports TABLE (
-- Holds employees added with each pass thru source employees table
EmployeeID int,
   EmployeeFirstName     nvarchar(10),
   EmployeeLastName nvarchar(20),
Title nvarchar(30),
TitleOfCourtesy nvarchar(25),
Extension nvarchar(4),
   ManagerID int,
ProcessedState tinyint
DEFAULT 0
)


--Begin by adding employees who report to the Manager directly.
INSERT INTO @tblReports
SELECT EmployeeID, FirstName, LastName, Title, TitleOfCourtesy,
Extension, ReportsTo, @PREPROCESSED
FROM Employees
WHERE ReportsTo = @iEmployeeID

--Save number of direct reports
SET @iRowsAdded = @@ROWCOUNT

-- Loop through Employees table until no more iterations are necessary
-- (e.g., no more rows added) to add all indirect reports.
WHILE @iRowsAdded > 0
BEGIN
--Set just added employees ProcessedState to PROCESSING
-- (for first pass)
UPDATE @tblReports
SET ProcessedState = @PROCESSING
WHERE ProcessedState = @PREPROCESSED

--Add employees who report to Managers in
-- ProcessedState = PROCESSING
INSERT INTO @tblReports
SELECT e.EmployeeID, e.FirstName, e.LastName, e.Title,
e.TitleOfCourtesy, e.Extension, e.ReportsTo, @PREPROCESSED
FROM Employees e
INNER JOIN @tblReports r ON e.ReportsTo = r.EmployeeID
WHERE r.ProcessedState = @PROCESSING
AND e.ReportsTo <> @iEmployeeID


--Save number of rows added for this iteration
SET @iRowsAdded = @@ROWCOUNT

--Set ProcessedState to POSTPROCESSED for Managers whose
--reports were added in this iteration
UPDATE @tblReports
SET ProcessedState = @POSTPROCESSED
WHERE ProcessedState = @PROCESSING
END

--Save all data to output table
INSERT INTO @ManagerReports
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Title,
TitleOfCourtesy, Extension, ManagerID
FROM @tblReports
   RETURN
END

La salida de esta función sería utilizada de manera semejante como tabla de estándar. El cuadro 1 demuestra JOINING de la salida de GetManagerReports con el tabla de los empleados para generar un listado de la estructura de organización del Northwind Company:

Llamando Funciones

Hay algunas idiosincrasias de la sintaxis al invocar funciones definidas por el usuario. SQL 2000 proporciona algunas funciones definidas por el usuario a nivel sistema en la base de datos Master. Estas funciones del sistema se invocan con un sintaxis levemente distinta a las que usted puede crear. Las funciones del sistema que devuelven un tabla tienen la sintaxis siguiente:

::function_name ([argument_expr], [,...])

System functions that return a scalar value use this syntax:

function_name ([argument_expr], [,...])

User-created scalar and rowset functions are invoked in exactly the same
manner.  The syntax for invoking a user-created function looks like this:

[database_name] owner_name. function_name ([argument_expr], [,...])

Limitaciones

Las funciones definidas por el usuario tienen algunas restricciones. No todas las sentencias SQL son válidas dentro de una función. Las listas siguientes enumeran las operaciones válidas e inválidas de la funciónes:

Válido:

Las sentencias de asignación
Las sentencias de Control de Flujo
Sentencias SELECT y modificacion de variables locales
Operaciones de cursores sobre variables locales Sentencias INSERT, UPDATE, DELETE con ariables Locales

Inválidas:

Armar funciones no determinadas como GetDate()
Sentencias de modificacion o actualizacion de tablas o vistas
Operaciones CURSOR FETCH que devuelven datos del cliente

Implicaciones De Performance Usar UDFs afectará la performance de las querys. El impacto de performance depende de cómo y de donde usted utiliza una función definida por el usuario. Usted debe tener precaucion al implementar funciones en sus consultas y realiza pruebas de benchmarking para asegurar que las ventajas de usar sus funciones exceden los costes de performance.

La función siguiente valida que un número de serie que sigue un modelo específico y las porciones del número de serie corresponden con un algoritmo específico para un tipo del producto.

Columnas computadas

Las funciones escalares se pueden utilizar para crear columnas calculadas en una definicion de tabla. Los argumentos de las funciones calculadas, columnas del tabla, constantes, o funciones incorporadas. Este ejemplo muestra un tabla que utilice una función del volumen para calcular el volumen de un envase

CREATE FUNCTION dbo.Volume ( @dHeight decimal(5,2),
@dLength decimal(5,2),
@dWidth decimal(5,2) )
RETURNS decimal (15,4)
AS
BEGIN
RETURN (@dHeight * @dLength * @dWidth )
END


CREATE TABLE dbo.Container
(
ContainerID int NOT NULL
PRIMARY KEY,
MaterialID int NOT NULL
REFERENCES Material(MaterialID),
ManufacturerID int NOT NULL
REFERENCES
Manufacturer(ManufacturerID)
Height decimal(5,2) NOT NULL,
Length decimal(5,2) NOT NULL,
Width decimal(5,2) NOT NULL,
Volume AS
(
dbo.Volume( Height, Length, Width )
)
)

Conclusión

Por lo visto, las funciones definidas por el usuario proporciona muchas más opciones de programación que había antes de que UDFs fueran incluidos en el lenguaje del SQL. Los programadores de SQL Server han esperado mucho tiempo las funciones y Microsoft lo a hecho posible con SQL 2000. Las ventajas de la reutilidad del código y correccion de problemas en una sola rutina pueden ser realizadas incorporando UDFs en nuestros diseños.

<-- Primera Parte