Funktionstypen
SQL Server 2005 unterstützt benutzerdefinierte Funktionen sowie integrierte Funktionen (Systemfunktionen).
Skalarfunktionen
Benutzerdefinierte Skalarfunktionen geben einen einzelnen Datenwert des definierten Datentyps in einer RETURNS-Klausel zurück. Bei einer Inlineskalarfunktion gibt es keinen Funktionshauptteil; der Skalarwert ist das Ergebnis einer einzelnen SELECT-Anweisung. Bei einer aus mehreren Anweisungen bestehenden Skalarfunktion enthält der in einem BEGIN…END-Block definierte Hauptteil der Funktion eine Reihe von Transact-SQL-Anweisungen, die den einzelnen Wert zurückgeben. Der Rückgabetyp kann ein beliebiger Datentypen mit Ausnahme von text, ntext, image, cursor und timestamp sein.
Die folgenden Beispiele erstellen Skalarfunktionen mit mehreren Anweisungen. Die Funktion nimmt einen Eingabewert (ProductID) an und gibt einen einzelnen Datenwert zurück, der die aggregierte Menge des Lagerbestands für das angegebene Produkt darstellt.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
GO
Im folgenden Beispiel wird die ufnGetInventoryStock-Funktion verwendet, um den aktuellen Lagerbestand für Produkte mit einer ProductModelID zwischen 75 und 80 zurückzugeben.
USE AdventureWorks;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO
Tabellenwertfunktionen
Benutzerdefinierte Tabellenwertfunktionen geben einen table-Datentyp zurück. Bei einer Inlinefunktion mit Tabellenrückgabe gibt es keinen Funktionshauptteil; die Tabelle ist das Resultset einer einzelnen SELECT-Anweisung.
Das folgende Beispiel erstellt eine Inline-Tabellenwertfunktion. Die Funktion nimmt einen Eingabeparameter (eine Kunden-ID (Geschäfts-ID)) an und gibt die Spalten ProductID, Name sowie das Aggregat der bisherigen Verkaufseinnahmen dieses Jahres als YTD Total für jedes Produkt zurück, das an das Geschäft verkauft wurde.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
Das folgende Beispiel ruft die Funktion auf und gibt die Kunden-ID 602 an.
SELECT * FROM Sales.ufn_SalesByStore (602);
Bei einer aus mehreren Anweisungen bestehenden Tabellenwertfunktion enthält der in einem BEGIN…END-Block definierte Hauptteil der Funktion eine Reihe von Transact-SQL-Anweisungen, die Zeilen in der Tabelle, die zurückgegeben wird, erstellen und einfügen.
Das folgende Beispiel erstellt eine Tabellenwertfunktion. Die Funktion nimmt einen einzelnen Eingabeparameter (EmployeeID) an und gibt eine Liste aller Mitarbeiter zurück, die dem angegebenen Mitarbeiter direkt oder indirekt unterstellt sind. Die Funktion wird dann unter Angabe der Mitarbeiternummer 109 aufgerufen.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO
Integrierte Funktionen
Integrierte Funktionen werden von SQL Server bereitgestellt, um Sie beim Ausführen einer Vielzahl von Operationen zu unterstützen. Sie können nicht geändert werden. Sie können integrierte Funktionen in Transact-SQL-Anweisungen für die folgenden Aufgaben verwenden:
- Zugreifen auf Informationen aus SQL Server-Systemtabellen, ohne direkt auf die Systemtabellen zugreifen zu müssen. Weitere Informationen finden Sie unter Verwenden von Systemfunktionen.
- Ausführen allgemeiner Aufgaben, z. B. SUM, GETDATE oder IDENTITY. Weitere Informationen finden Sie unter Funktionen (Transact-SQL).
Integrierte Funktionen geben Skalar- oder table-Datentypen zurück. @@ERROR gibt z. B. 0 zurück, nachdem die letzte Transact-SQL-Anweisung erfolgreich ausgeführt wurde. Wenn die Anweisung einen Fehler generiert hat, gibt @@ERROR die Fehlernummer zurück. Die Funktion SUM(parameter) gibt die Summe aller Werte für den Parameter zurück.
Siehe auch
Andere Ressourcen
Grundlegendes zu benutzerdefinierten Funktionen