Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine)Create User-defined Functions (Database Engine)

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL DatabaseAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database

In diesem Thema wird beschrieben, wie eine benutzerdefinierte SQL ServerSQL Server -Funktion (User-defined Function, UDF) mit Transact-SQLTransact-SQLerstellt wird.This topic describes how to create a user-defined function (UDF) in SQL ServerSQL Server by using Transact-SQLTransact-SQL.

VorbereitungenBefore You Begin

EinschränkungenLimitations and restrictions

  • Mit benutzerdefinierten Funktionen können keine Aktionen ausgeführt werden, die den Status einer Datenbank ändern.User-defined functions cannot be used to perform actions that modify the database state.

  • Benutzerdefinierte Funktionen dürfen keine OUTPUT INTO-Klausel enthalten, deren Ziel eine Tabelle ist.User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

  • Von benutzerdefinierten Funktionen können nicht mehrere Resultsets zurückgegeben werden.User-defined functions can not return multiple result sets. Falls mehrere Resultsets zurückgegeben werden müssen, verwenden Sie eine gespeicherte Prozedur.Use a stored procedure if you need to return multiple result sets.

  • Die Fehlerbehandlung ist in einer benutzerdefinierten Funktion eingeschränkt.Error handling is restricted in a user-defined function. Eine benutzerdefinierte Funktion bietet keine Unterstützung für TRY...CATCH, @ERROR oder RAISERROR.A UDF does not support TRY...CATCH, @ERROR or RAISERROR.

  • Von benutzerdefinierten Funktionen kann zwar keine gespeicherte Prozedur, aber eine erweiterte gespeicherte Prozedur aufgerufen werden.User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

  • Von benutzerdefinierten Funktionen können kein dynamisches SQL bzw. keine temporären Tabellen verwendet werden.User-defined functions cannot make use of dynamic SQL or temp tables. Tabellenvariablen sind zulässig.Table variables are allowed.

  • SET-Anweisungen sind in einer benutzerdefinierten Funktion nicht zulässig.SET statements are not allowed in a user-defined function.

  • Die FOR XML-Klausel ist nicht zulässig.The FOR XML clause is not allowed.

  • Benutzerdefinierte Funktionen können geschachtelt werden. Dies bedeutet, dass eine benutzerdefinierte Funktion eine andere aufrufen kann.User-defined functions can be nested; that is, one user-defined function can call another. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Funktion mit der Ausführung beginnt, und wird wieder um eins erniedrigt, wenn die aufgerufene Funktion die Ausführung beendet.The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. Benutzerdefinierte Funktionen unterstützen bis zu 32 geschachtelte Ebenen.User-defined functions can be nested up to 32 levels. Ein Überschreiten der maximalen Schachtelungsebenen verursacht das Fehlschlagen der gesamten Funktionsaufrufskette.Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Alle Verweise auf verwalteten Code von einer benutzerdefinierten Transact-SQL-Funktion aus gelten hinsichtlich des Maximums von 32 Schachtelungsebenen als eine Ebene.Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methoden, die aus verwaltetem Code aufgerufen werden, werden nicht mitgezählt.Methods invoked from within managed code do not count against this limit.

  • Die folgenden Service Broker-Anweisungen können nicht in die Definition einer benutzerdefinierten Transact-SQLTransact-SQL-Funktion aufgenommen werden:The following Service Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

    • BEGIN DIALOG CONVERSATION

    • END CONVERSATION

    • GET CONVERSATION GROUP

    • MOVE CONVERSATION

    • RECEIVE

    • SEND

BerechtigungenPermissions

Erfordert die CREATE FUNCTION-Berechtigung in der Datenbank und die ALTER-Berechtigung für das Schema, in dem die Funktion erstellt wird.Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. Wenn die Funktion einen benutzerdefinierten Typ angibt, wird die EXECUTE-Berechtigung für den Typ benötigt.If the function specifies a user-defined type, requires EXECUTE permission on the type.

SkalarfunktionenScalar Functions

Im folgenden Beispiel wird eine Skalarfunktion (benutzerdefinierte Skalarfunktion) mit mehreren Anweisungen in der AdventureWorks2012AdventureWorks2012-Datenbank erstellt.The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012AdventureWorks2012 database. 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.The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

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; 

Im folgenden Beispiel wird die ufnGetInventoryStock -Funktion verwendet, um den aktuellen Lagerbestand für Produkte mit einer ProductModelID zwischen 75 und 80 zurückzugeben.The following example uses the ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;  

Hinweis

Weitere Informationen und Beispiele für Skalarfunktionen finden Sie unter CREATE FUNCTION (Transact-SQL).For more information and examples of scalar functions, see CREATE FUNCTION (Transact-SQL).

TabellenwertfunktionenTable-Valued Functions

Im folgenden Beispiel wird eine Inline-Tabellenwertfunktion in der AdventureWorks2012AdventureWorks2012-Datenbank erstellt.The following example creates an inline table-valued function (TVF) in the AdventureWorks2012AdventureWorks2012 database. 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.The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

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  
);  

Das folgende Beispiel ruft die Funktion auf und gibt die Kunden-ID 602 an.The following example invokes the function and specifies customer ID 602.

SELECT * FROM Sales.ufn_SalesByStore (602);  

Im folgenden Beispiel wird eine Tabellenwertfunktion mit mehreren Anweisungen in der AdventureWorks2012AdventureWorks2012-Datenbank erstellt.The following example creates a multi-statement table-valued function (MSTVF) in the AdventureWorks2012AdventureWorks2012 database. 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.The function takes a single input parameter, an EmployeeID and returns a list of all the employees who report to the specified employee directly or indirectly. Die Funktion wird dann unter Angabe der Mitarbeiternummer 109 aufgerufen.The function is then invoked specifying employee ID 109.

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  

Das folgende Beispiel ruft die Funktion auf und gibt die Mitarbeiternummer 1 an.The following example invokes the function and specifies employee ID 1.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);  

Hinweis

Weitere Informationen und Beispiele für Inline-Tabellenwertfunktionen und Tabellenwertfunktionen mit mehreren Anweisungen finden Sie unter CREATE FUNCTION (Transact-SQL).For more information and examples of inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see CREATE FUNCTION (Transact-SQL).

Bewährte MethodenBest Practices

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.If a user-defined function (UDF) is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. Es wird empfohlen, eine der folgenden Methoden zu implementieren, damit die Funktion aufgrund von Änderungen an den zugrunde liegenden Objekten nicht veraltet ist:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • Geben Sie beim Erstellen der benutzerdefinierten Funktion die WITH SCHEMABINDING-Klausel an.Specify the WITH SCHEMABINDING clause when you are creating the UDF. Hiermit 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.This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

  • 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.Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

Wenn Sie eine benutzerdefinierte Funktion erstellen, die nicht auf Daten zugreift, geben Sie die Option SCHEMABINDING an.If creating a UDF that does not access data, specify the SCHEMABINDING option. Dadurch wird verhindert, dass der Abfrageoptimierer unnötige Spool-Operatoren für Abfragepläne generiert, die diese benutzerdefinierten Funktionen enthalten.This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. Weitere Informationen zu Spoolvorgängen finden Sie unter Referenz zu logischen und physischen Showplanoperatoren.For more information on spools, see Showplan Logical and Physical Operators Reference. Weitere Informationen zum Erstellen einer schemagebundenen Funktion finden Sie unter Schemagebundene Funktionen.For more information on creating a schema bound function, see Schema-bound functions.

Ein Verknüpfen mit einer Tabellenwertfunktion mit mehreren Anweisungen in einer FROM-Klausel ist möglich, kann jedoch zu Leistungseinbußen führen.Joining to an MSTVF in a FROM clause is possible, but can result in poor performance. SQL ServerSQL 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.is unable to use all the optimized techniques on some statements that can be included in a MSTVF, resulting in a suboptimal query plan. Um die bestmögliche Leistung zu erzielen, sollten nach Möglichkeit anstelle von Funktionen Joins zwischen Basistabellen verwendet werden.To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

Wichtig

Die festgelegte Kardinalitätsschätzung von Tabellenwertfunktionen mit mehreren Anweisungen beträgt ab SQL Server 2014 (12.x)SQL Server 2014 (12.x) „100“ und in früheren Versionen von SQL ServerSQL Server „1“.MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions.
Ab SQL Server 2017 (14.x)SQL Server 2017 (14.x) kann beim Optimieren eines Ausführungsplans, der Tabellenwertfunktionen mit mehreren Anweisungen verwendet, eine verschachtelte Ausführung genutzt werden, was dazu führt, dass die tatsächliche Kardinalität anstelle der oben genannten Heuristik verwendet wird.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), optimizing an execution plan that uses MSTVFs can leverage interleaved execution, which results in using actual cardinality instead of the above heuristics.
Weitere Informationen finden Sie unter Verschachtelte Ausführung mit Tabellenwertfunktionen mit mehreren Anweisungen.For more information, see Interleaved execution for multi-statement table valued functions.

Hinweis

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.ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. 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.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Weitere InformationenSee Also

Benutzerdefinierte Funktionen User-Defined Functions
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER FUNCTION (Transact-SQL) ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL) DROP PARTITION FUNCTION (Transact-SQL)
Weitere Beispiele in der CommunityMore examples in the community