Fonctions table définies par l'utilisateur
Les fonctions définies par l'utilisateur qui retournent un type de données table peuvent efficacement se substituer aux vues. Ces fonctions sont appelées fonctions table. Une fonction table définie par l'utilisateur peut être utilisée dans les requêtes Transact-SQL, aux mêmes emplacements que les expressions de table ou de vue. Alors que les vues sont limitées à une seule instruction SELECT, les fonctions définies par l'utilisateur peuvent contenir davantage d'instructions, ce qui permet d'élaborer une logique plus efficace que dans les vues.
Une fonction table définie par l'utilisateur peut également remplacer les procédures stockées qui retournent un seul jeu de résultats. La table retournée par une fonction définie par l'utilisateur peut être référencée dans la clause FROM d'une instruction Transact-SQL ; cette fonctionnalité est interdite aux procédures stockées qui retournent des jeux de résultats.
Composants d'une fonction table définie par l'utilisateur
Dans une fonction table définie par l'utilisateur :
La clause RETURNS définit un nom de variable de retour locale pour la table retournée par la fonction, ainsi que le format de la table. Le nom de la variable de retour locale est de portée locale au sein de la fonction.
Les instructions Transact-SQL contenues dans le corps de la fonction créent et insèrent des lignes dans la variable de retour définie par la clause RETURNS.
Lorsqu'une instruction RETURN est exécutée, les lignes insérées dans la variable sont retournées en guise de sortie tabulaire de la fonction. L'instruction RETURN ne peut pas contenir d'argument.
Aucune instruction Transact-SQL d'une fonction table ne peut retourner un jeu de résultats directement à un utilisateur. Les informations que la fonction peut retourner à l'utilisateur se limitent à la table.
Notes
L'option de table text in row prend automatiquement la valeur 256 dans le cas d'une table retournée par une fonction définie par l'utilisateur. Cette valeur ne peut pas être modifiée. Les instructions READTEXT, WRITETEXT et UPDATETEXT ne peuvent pas être utilisées pour lire ou écrire des parties de colonnes de type text, ntext ou image dans la table. Pour plus d'informations, consultez Données de ligne.
Exemple
L'exemple suivant crée la fonction dbo.ufnGetContactInformation et présente les composants de la fonction table. Dans cette fonction, le nom de la variable de retour locale est @retContactInformation. Les instructions contenues dans le corps de la fonction insèrent des lignes dans cette variable pour élaborer le résultat de table retourné par la fonction.
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
L'exemple suivant utilise la fonction table dbo.ufnGetContactInformation dans la clause FROM de deux instructions 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