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 AdventureWorks;
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 = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc
INNER JOIN Person.ContactType AS ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc
INNER JOIN Person.ContactType AS ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual AS i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
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 AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO
Vea también
Conceptos
Funciones en línea definidas por el usuario
Funciones deterministas y no deterministas
Volver a escribir procedimientos almacenados como funciones
Otros recursos
Diseñar funciones definidas por el usuario