Funciones definidas por el usuario con valores de tabla
Las funciones definidas por el usuario que devuelven un tipo de datos table pueden ser unas eficaces alternativas a las vistas. Esas funciones se conocen como funciones con valores de tabla. Una función definida por el usuario con valores de tabla se puede usar donde se permiten las expresiones de vista o de tabla en las consultas Transact-SQL. Mientras que las vistas se limitan a una única instrucción SELECT, las funciones definidas por el usuario pueden contener instrucciones adicionales que permiten una lógica más eficaz que en las vistas.
Una función definida por el usuario con valores de tabla también puede reemplazar procedimientos almacenados que devuelven un solo conjunto de resultados. En la cláusula FROM de una instrucción Transact-SQL es posible hacer referencia a la tabla que devuelve una función definida por el usuario, pero esto no es posible con los procedimientos almacenados que devuelven conjuntos de resultados.
Componentes de una función definida por el usuario con valores de tabla
En una función definida por el usuario con valores de tabla:
La cláusula RETURNS define el nombre de una variable de retorno local para la tabla devuelta por la función. La cláusula RETURNS también define el formato de la tabla. El nombre de una variable de retorno local tiene un ámbito local dentro de la función.
Las instrucciones Transact-SQL del cuerpo de la función generan e insertan filas en la variable de retorno definida por la cláusula RETURNS.
Al ejecutar una instrucción RETURN, las filas insertadas en la variable se devuelven desde la función en formato tabular. La instrucción RETURN no puede tener un argumento.
Ninguna instrucción Transact-SQL de una función con valores de tabla puede devolver un conjunto de resultados directamente a un usuario. La única información que la función puede devolver al usuario es el tipo de datos table devuelto por la función.
Nota
La opción de tabla text in row se define automáticamente en 256 para una tabla devuelta por una función definida por el usuario. Esto no puede modificarse. Las instrucciones READTEXT, WRITETEXT y UPDATETEXT no pueden utilizarse para leer o escribir partes de las columnas text, ntext e image de la tabla. Para obtener más información, vea Datos consecutivos.
Ejemplo
En el siguiente ejemplo se crea la función dbo.ufnGetContactInformation y se muestran los componentes de la función con valores de tabla. En esta función, el nombre de la variable de retorno local es @retContactInformation. Las instrucciones del cuerpo de la función insertan filas en esta variable para generar la tabla resultante devuelta por la función.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @ContactID;
-- Get contact job title
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
ELSE NULL
END;
-- Get contact type
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN')
THEN 'Consumer'
-- Check for general contact
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC')
THEN 'General Contact'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
En el siguiente ejemplo se utiliza la función con valores de tabla dbo.ufnGetContactInformation en la cláusula FROM de dos instrucciones SELECT.
USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO