Funzioni definite dall'utente con valori 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 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
Negli esempi seguenti viene utilizzata la funzione valutata a livello di tabella dbo.ufnGetContactInformation nella clausola FROM delle due istruzioni 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