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.
.gif "ms191165.note(fr-fr,SQL.90).gif") Remarque : |
|---|
| 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 dans la 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 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
L'exemple suivant utilise la fonction table dbo.ufnGetContactInformation dans la clause FROM de deux instructions 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
Voir aussi
Concepts
Fonctions définies par l'utilisateur inline
Fonctions déterministes et non déterministes
Réécriture de procédures stockées en fonctions
Autres ressources
Création de fonctions définies par l'utilisateur