Condividi tramite


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 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