• Artikel

Benutzerdefinierte Tabellenwertfunktionen

Benutzerdefinierte Funktionen, die einen table-Datentyp zurückgeben, können leistungsfähige Alternativen zu Sichten sein. Diese Funktionen werden als Tabellenwertfunktionen bezeichnet. Eine Tabellenwertfunktion kann überall dort verwendet werden, wo Tabellen- oder Sichtausdrücke in Transact-SQL-Abfragen zulässig sind. Sichten sind auf eine einzelne SELECT-Anweisung beschränkt, während benutzerdefinierte Funktionen zusätzliche Anweisungen enthalten können, die eine leistungsfähigere Logik als Sichten ermöglichen.

Eine benutzerdefinierte Tabellenwertfunktion kann auch gespeicherte Prozeduren ersetzen, die ein einzelnes Resultset zurückgeben. Auf die von einer benutzerdefinierten Funktion zurückgegebene Tabelle kann in der FROM-Klausel einer Transact-SQL-Anweisung verwiesen werden, während dies bei gespeicherten Prozeduren, die Resultsets zurückgeben, nicht möglich ist.

Komponenten einer benutzerdefinierten Tabellenwertfunktion

Folgendes gilt für eine benutzerdefinierte Tabellenwertfunktion:

  • Die RETURNS-Klausel definiert einen lokalen Rückgabevariablennamen für die Tabelle, die von der Funktion zurückgegeben wird. Die RETURNS-Klausel definiert außerdem das Format der Tabelle. Der lokale Rückgabevariablenname hat innerhalb der Funktion einen lokalen Gültigkeitsbereich.

  • Durch die Transact-SQL-Anweisungen im Hauptteil der Funktion werden Zeilen in der Rückgabevariablen, die durch die RETURNS-Klausel definiert wird, erstellt und in diese eingefügt.

  • Beim Ausführen einer RETURN-Anweisung werden die Zeilen, die in die Variable eingefügt werden, als Tabellenausgabe der Funktion zurückgegeben. Für die RETURN-Anweisung ist kein Argument zulässig.

Transact-SQL-Anweisungen in einer Tabellenwertfunktion können ein Resultset nicht direkt an einen Benutzer zurückgeben. Die einzige Information, die die Funktion an den Benutzer zurückgeben kann, ist der von der Funktion zurückgegebene table-Wert.

HinweisHinweis

Die text in row-Tabellenoption wird für eine Tabelle, die von einer benutzerdefinierten Funktion zurückgegeben wird, automatisch auf 256 festgelegt. Diese Einstellung kann nicht geändert werden. Mit den READTEXT-, WRITETEXT- und UPDATETEXT-Anweisungen können Teile von Spalten des text-, ntext- oder image-Datentyps nicht gelesen oder geschrieben werden. Weitere Informationen finden Sie unter Daten in Zeilen.

Beispiel

Das folgende Beispiel erstellt die dbo.ufnGetContactInformation-Funktion und zeigt die Komponenten der Tabellenwertfunktion. In dieser Funktion lautet der Name der lokalen Rückgabevariable @retContactInformation. Anweisungen im Hauptteil der Funktion erstellen das table-Ergebnis, das von der Funktion zurückgegeben wird, indem Zeilen in diese Variable eingefügt werden.

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

Das folgende Beispiel verwendet die dbo.ufnGetContactInformation-Tabellenwertfunktion in der FROM-Klausel zweier SELECT-Anweisungen.

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