Funzioni definite dall'utente valutate a livello di tabella
Le funzioni definite dall'utente che restituiscono un valore di tipo table possono rappresentare una valida alternativa alle viste. Queste funzioni vengono dette funzioni valutate a livello di tabella. È possibile utilizzare una funzione definita dall'utente valutata a livello di tabella in tutti i casi in cui nelle query Transact-SQL sono consentite espressioni di tabella o vista. Per le viste è possibile utilizzare una sola istruzione SELECT; tuttavia, le funzioni definite dall'utente possono contenere istruzioni aggiuntive che consentono una logica più efficace di quella consentita nelle viste.
Una funzione definita dall'utente valutata a livello di tabella può inoltre sostituire stored procedure che restituiscono un solo set di risultati. La clausola FROM di un'istruzione Transact-SQL può contenere il riferimento alla tabella restituita da una funzione definita dall'utente, mentre non può contenere un riferimento alle stored procedure che restituiscono set di risultati.
Componenti di una funzione definita dall'utente valutata a livello di tabella
In una funzione definita dall'utente valutata a livello di tabella
- La clausola RETURNS definisce il nome di una variabile locale restituita per la tabella restituita dalla funzione. La clausola RETURNS definisce anche il formato della tabella. L'ambito del nome della variabile locale restituita è locale all'interno della funzione.
- Le istruzioni Transact-SQL nel corpo della funzione generano e inseriscono righe nella variabile restituita definita dalla clausola RETURNS.
- Quando viene eseguita un'istruzione RETURN, le righe inserite nella variabile vengono restituite come output tabulare della funzione. L'istruzione RETURN non può avere argomenti.
Nessuna istruzione Transact-SQL in una funzione valutata a livello di tabella può restituire un set di risultati direttamente a un utente. La funzione può restituire all'utente solo il valore table restituito dalla funzione.
[!NOTA] L'opzione di tabella text in row viene impostata automaticamente su 256 per una tabella restituita da una funzione definita dall'utente. Questo valore non può essere modificato. Non è possibile utilizzare le istruzioni READTEXT, WRITETEXT e UPDATETEXT per leggere o scrivere parti di qualsiasi colonna text, ntext o image della tabella. Per ulteriori informazioni, vedere Dati all'interno di righe.
Esempio
Nell'esempio seguente viene creata la funzione dbo.ufnGetContactInformation e vengono illustrati i componenti della funzione valutata a livello di tabella. In questa funzione, il nome della variabile locale restituita è @retContactInformation. Le istruzioni nel corpo della funzione inseriscono righe in questa variabile per creare il risultato restituito dalla funzione.
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
Negli esempi seguenti viene utilizzata la funzione valutata a livello di tabella dbo.ufnGetContactInformation nella clausola FROM delle due istruzioni 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
Vedere anche
Concetti
Funzioni inline definite dall'utente
Funzioni deterministiche e non deterministiche
Riformulazione di stored procedure come funzioni
Altre risorse
Progettazione di funzioni definite dall'utente