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

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

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