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.
.gif "Hinweis") Hinweis |
|---|
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