Funktionstypen
SQL Server 2008 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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 '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
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @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 AdventureWorks2008R2;
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,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
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 Integrierte 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.