Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric
In diesem Artikel wird beschrieben, wie Sie mithilfe von Transact-SQL eine benutzerdefinierte Funktion (User-Defined Function, UDF) in SQL Server erstellen.
Einschränkungen
Mit benutzerdefinierten Funktionen können keine Aktionen ausgeführt werden, die den Status einer Datenbank ändern.
Benutzerdefinierte Funktionen dürfen keine OUTPUT INTO-Klausel enthalten, deren Ziel eine Tabelle ist.
Benutzerdefinierte Funktionen können nicht mehrere Resultsets zurückgeben. Falls mehrere Resultsets zurückgegeben werden müssen, verwenden Sie eine gespeicherte Prozedur.
Die Fehlerbehandlung ist in einer benutzerdefinierten Funktion eingeschränkt. Eine UDF unterstützt weder TRY...CATCH, @ERROR noch RAISERROR.
Benutzerdefinierte Funktionen können keine gespeicherte Prozedur, aber eine erweiterte gespeicherte Prozedur aufrufen.
Benutzerdefinierte Funktionen können kein dynamisches SQL und keine temporären Tabellen verwenden. Tabellenvariablen sind zulässig.
SET-Anweisungen sind in einer benutzerdefinierten Funktion nicht erlaubt (z.B. SET NOCOUNT ON;). Variablenwerte können mit SET zugewiesen werden.
Die Klausel FOR XML ist nicht zulässig.
Verschachtelte benutzerdefinierte Funktionen
Benutzerdefinierte Funktionen können geschachtelt werden. Das heißt, eine benutzerdefinierte Funktion kann eine andere aufrufen. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Funktion mit der Ausführung beginnt, und wird wieder um eins verringert, wenn die aufgerufene Funktion die Ausführung beendet.
Benutzerdefinierte Funktionen unterstützen bis zu 32 geschachtelte Ebenen. Ein Überschreiten der maximalen Schachtelungsebenen verursacht einen Fehler für die gesamte Funktionsaufrufskette. Alle Verweise auf verwalteten Code von einer benutzerdefinierten Transact-SQL-Funktion aus gelten hinsichtlich des Maximums von 32 Schachtelungsebenen als eine Ebene.
Methoden, die aus verwaltetem Code aufgerufen werden, werden nicht mitgezählt.
Service Broker-Anweisungen
Die folgenden Service Broker-Anweisungen können nicht in die Definition einer benutzerdefinierten Transact-SQL-Funktion eingeschlossen werden:
BEGIN DIALOG CONVERSATIONEND CONVERSATIONGET CONVERSATION GROUPMOVE CONVERSATIONRECEIVESEND
Nebeneffektfunktionen
Die folgenden nicht deterministischen integrierten Funktionen können nicht in einer Transact-SQL benutzerdefinierten Funktion (UDF) verwendet werden.
NEWIDNEWSEQUENTIALIDRANDTEXTPTR
Wenn Sie auf eine dieser Funktionen in einer UDF verweisen, wird die folgende Fehlermeldung angezeigt:
Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.
Eine Liste der integrierten deterministischen und nicht-deterministischen Systemfunktionen finden Sie unter Deterministische und nicht-deterministische Funktionen.
Um dieses Problem zu umgehen, können Sie die Nebeneffektfunktion in einer Ansicht umschließen und die Ansicht aus einer Funktion aufrufen.
Berechtigungen
Erfordert die CREATE FUNCTION-Berechtigung in der Datenbank und die ALTER-Berechtigung für das Schema, in dem die Funktion erstellt wird. Wenn die Funktion einen benutzerdefinierten Typ angibt, wird die EXECUTE-Berechtigung für den Typ benötigt.
Beispiele für Skalarfunktionen
Skalarfunktion (benutzerdefinierte Skalarfunktion)
Das folgende Beispiel erzeugt eine mehrfache Skalarfunktion (skalare UDF) in der AdventureWorks2025-Datenbank. 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.
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 AS INT;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory AS p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END
Im folgenden Beispiel wird die ufnGetInventoryStock -Funktion verwendet, um den aktuellen Lagerbestand für Produkte mit einer ProductModelID zwischen 75 und 80 zurückzugeben.
SELECT ProductModelID,
Name,
dbo.ufnGetInventoryStock(ProductID) AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 AND 80;
Weitere Informationen und Beispiele für skalare Funktionen finden Sie unter CREATE FUNCTION.
Beispiele für Tabellenwertfunktionen
Inline-Tabellenwertfunktion
Das folgende Beispiel erzeugt eine inline-tabellenwertige Funktion (TVF) in der AdventureWorks2025-Datenbank. Die Funktion nimmt einen Eingabeparameter (eine Kunden-ID (Geschäfts-ID)) an und gibt die Spalten ProductID, Namesowie das Aggregat der bisherigen Verkaufseinnahmen dieses Jahres als YTD Total für jedes Produkt zurück, das an das Geschäft verkauft wurde.
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
INNER JOIN Sales.SalesOrderDetail AS SD
ON SD.ProductID = P.ProductID
INNER JOIN Sales.SalesOrderHeader AS SH
ON SH.SalesOrderID = SD.SalesOrderID
INNER JOIN Sales.Customer AS C
ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
Das folgende Beispiel ruft die Funktion auf und gibt die Kunden-ID 602 an.
SELECT *
FROM Sales.ufn_SalesByStore(602);
Tabellenwertfunktion mit mehreren Anweisungen
Das folgende Beispiel erzeugt eine Mehrausweisungs-Tabellenwertfunktion (MSTVF) in der Datenbank AdventureWorks2025. 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.
IF OBJECT_ID(N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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 (
-- Get the initial list of Employees for Manager n
SELECT e.BusinessEntityID,
e.OrganizationNode,
p.FirstName,
p.LastName,
e.JobTitle,
0
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS 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 AS e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person AS 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
Das folgende Beispiel ruft die Funktion auf und gibt die Mitarbeiternummer 1 an.
SELECT EmployeeID,
FirstName,
LastName,
JobTitle,
RecursionLevel
FROM dbo.ufn_FindReports(1);
Weitere Informationen zu Inline-Tabellenwertfunktionen und Tabellenwertfunktionen mit mehreren Anweisungen sowie einige Beispiele finden Sie unter CREATE FUNCTION.
Bewährte Methoden
Wenn eine benutzerdefinierte Funktion nicht mit der SCHEMABINDING-Klausel erstellt wurde, können sich die an zugrunde liegenden Objekten vorgenommenen Änderungen auf die Definition der Funktion auswirken und bei Aufruf der Funktion zu unerwarteten Ergebnissen führen. Es wird empfohlen, eine der folgenden Methoden zu implementieren, um sicherzustellen, dass die Funktion nicht aufgrund von Änderungen an den zugrunde liegenden Objekten veraltet:
Geben Sie beim Erstellen der benutzerdefinierten Funktion die
WITH SCHEMABINDING-Klausel an. Damit wird sichergestellt, dass die Objekte, auf die in der Funktionsdefinition verwiesen wird, nicht geändert werden können, es sei denn, die Funktion wird auch geändert.Führen Sie die gespeicherte Prozedur sp_refreshsqlmodule aus, nachdem Sie ein Objekt geändert haben, das in der Definition der benutzerdefinierten Funktion angegeben ist.
Wenn Sie eine UDF erstellen, die nicht auf Daten zugreift, geben Sie die Option SCHEMABINDING an, um zu verhindern, dass der Abfrageoptimierer unnötige Spool-Operatoren für Abfragepläne generiert, an denen diese UDFs beteiligt sind. Weitere Informationen zu Spools finden Sie unter Logische und physische Showplan-Operator-Referenz. Weitere Informationen zum Erstellen einer schemagebundenen Funktion finden Sie unter Schemagebundene Funktionen.
Ein Verknüpfen mit einer Tabellenwertfunktion mit mehreren Anweisungen in einer FROM-Klausel ist möglich, kann jedoch zu Leistungseinbußen führen. SQL Server kann für einige Anweisungen, die in einer Tabellenwertfunktion mit mehreren Anweisungen enthalten sein können, nicht alle optimierten Techniken verwenden, was zu einem suboptimalen Abfrageplan führt. Um die bestmögliche Leistung zu erzielen, sollten nach Möglichkeit anstelle von Funktionen Joins zwischen Basistabellen verwendet werden.
MSTVFs haben ab SQL Server 2014 (12.x) eine feste Kardinalitätsschätzung von 100 und 1 für frühere Versionen von SQL Server.
In SQL Server 2017 (14.x) und späteren Versionen kann die Optimierung eines Ausführungsplans, der MSTVFs verwendet, die verschachtelte Ausführung verwenden, was dazu führt, dass die tatsächliche Kardinalität anstelle der zuvor erwähnten Heuristik verwendet wird.
Weitere Informationen finden Sie unter Verschachtelte Ausführung mit Tabellenwertfunktionen mit mehreren Anweisungen.
ANSI_WARNINGS wird bei der Übergabe von Parametern in einer gespeicherten Prozedur oder in einer benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batchanweisung nicht berücksichtigt. Wird beispielsweise eine Variable als char(3) definiert und dann auf einen Wert festgelegt, der länger als drei Zeichen ist, werden die Daten auf die definierte Größe abgeschnitten, und die Anweisung INSERT oder UPDATE wird erfolgreich ausgeführt.