Door de gebruiker gedefinieerde functies maken (Database Engine)

2025-07-18

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

In dit artikel wordt beschreven hoe u een door de gebruiker gedefinieerde functie (UDF) maakt in SQL Server met behulp van Transact-SQL.

Beperkingen

Door de gebruiker gedefinieerde functies kunnen niet worden gebruikt om acties uit te voeren die de databasestatus wijzigen.

Door de gebruiker gedefinieerde functies kunnen geen OUTPUT INTO-component bevatten die een tabel als doel heeft.

Door de gebruiker gedefinieerde functies kunnen niet meerdere resultatensets retourneren. Gebruik een opgeslagen procedure als u meerdere resultatensets wilt retourneren.

Foutafhandeling is beperkt in een door de gebruiker gedefinieerde functie. Een UDF biedt geen ondersteuning voor TRY...CATCH, @ERRORof RAISERROR.

Door de gebruiker gedefinieerde functies kunnen geen opgeslagen procedure aanroepen, maar kunnen een uitgebreide opgeslagen procedure aanroepen.

Door de gebruiker gedefinieerde functies kunnen geen gebruik maken van dynamische SQL- of tijdelijke tabellen. Tabelvariabelen zijn toegestaan.

SET instructies zijn niet toegestaan in een door de gebruiker gedefinieerde functie (bijvoorbeeld SET NOCOUNT ON;). De toewijzing van variabele waarden kan gebruik maken van SET.

De FOR XML-clausule is niet toegestaan.

Geneste functies die door de gebruiker zijn gedefinieerd

Door de gebruiker gedefinieerde functies kunnen worden genest. Dat wil gezegd, één door de gebruiker gedefinieerde functie kan een andere aanroepen. Het nestniveau wordt verhoogd wanneer de aangeroepen functie wordt gestart en afneemt wanneer de aangeroepen functie de uitvoering voltooit.

Gebruikersgedefinieerde functies kunnen tot maximaal 32 niveaus worden genest. Als u de maximumniveaus van nesten overschrijdt, mislukt de hele aanroepende functieketen. Elke verwijzing naar beheerde code van een gebruikergedefinieerde Transact-SQL-functie telt als één niveau ten opzichte van de nestlimiet van 32 niveaus.

Methoden die vanuit beheerde code worden aangeroepen, tellen niet mee voor deze limiet.

Service Broker-instructies

De volgende Service Broker-instructies kunnen niet worden opgenomen in de definitie van een door de gebruiker gedefinieerde Transact-SQL functie:

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

Functies met neveneffecten

De volgende niet-deterministische ingebouwde functies kunnen niet worden gebruikt in een Transact-SQL door de gebruiker gedefinieerde functie (UDF).

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Als u naar een van deze functies in een UDF verwijst, krijgt u de volgende fout:

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

Zie Deterministische en niet-deterministische ingebouwde systeemfuncties voor een lijst met deterministische en niet-deterministische functies.

U kunt dit probleem omzeilen door de bijwerkingen veroorzakende functie in een view te verpakken en de view aan te roepen vanuit een functie.

Machtigingen

Vereist CREATE FUNCTION machtiging in de database en ALTER machtiging voor het schema waarin de functie wordt gemaakt. Als de functie een door de gebruiker gedefinieerd type opgeeft, is EXECUTE machtiging voor het type vereist.

Voorbeelden van scalaire functies

Scalaire functie (scalaire UDF)

In het volgende voorbeeld wordt een multi-statement gemaakt scalaire functie (scalaire UDF) in de AdventureWorks2022-database. De functie gebruikt één invoerwaarde, een ProductIDen retourneert één gegevenswaarde, de geaggregeerde hoeveelheid van het opgegeven product in de voorraad.

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

In het volgende voorbeeld wordt de functie ufnGetInventoryStock gebruikt om de huidige voorraadhoeveelheid te retourneren voor producten met een ProductModelID tussen 75 en 80.

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

Zie CREATE FUNCTIONvoor meer informatie en voorbeelden van scalaire functies.

Voorbeelden van functies met tabelwaarden

Inline tabelwaarde functie (TVF)

In het volgende voorbeeld wordt een inline tabelwaardefunctie (TVF) gemaakt in de Database AdventureWorks2022. De functie gebruikt één invoerparameter, een klant-id (winkel) en retourneert de kolommen ProductID, Nameen de cumulatieve omzet van het jaar tot heden als YTD Total voor elk product dat in de winkel wordt verkocht.

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

In het volgende voorbeeld wordt de functie aangeroepen en wordt de klant-id 602 opgegeven.

SELECT *
FROM Sales.ufn_SalesByStore(602);

Tabelwaardefunctie met meerdere instructies (MSTVF)

In het volgende voorbeeld wordt een tabelwaardefunctie met meerdere instructies (MSTVF) gemaakt in de AdventureWorks2022-database. De functie gebruikt één invoerparameter, een EmployeeID en retourneert een lijst met alle werknemers die direct of indirect rapporteren aan de opgegeven werknemer. De functie wordt vervolgens aangeroepen met de werknemer-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 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

In het volgende voorbeeld wordt de functie aangeroepen en wordt de werknemer-id 1 opgegeven.

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

Zie CREATE FUNCTIONvoor meer informatie en voorbeelden van inline-tabelwaardefuncties (inline-TVF's) en multi-statement tabelwaardefuncties (MSTVF's).

Goede praktijken

Als een door de gebruiker gedefinieerde functie (UDF) niet wordt gemaakt met de SCHEMABINDING component, kunnen wijzigingen die worden aangebracht in onderliggende objecten de definitie van de functie beïnvloeden en onverwachte resultaten opleveren wanneer deze worden aangeroepen. U wordt aangeraden een van de volgende methoden te implementeren om ervoor te zorgen dat de functie niet verouderd raakt vanwege wijzigingen in de onderliggende objecten:

Geef de WITH SCHEMABINDING-clausule op wanneer u de UDF maakt. Dit zorgt ervoor dat de objecten waarnaar wordt verwezen in de functiedefinitie niet kunnen worden gewijzigd, tenzij de functie ook wordt gewijzigd.
Voer de sp_refreshsqlmodule opgeslagen procedure uit nadat u een object hebt gewijzigd dat is opgegeven in de definitie van de UDF.

Als u een UDF maakt die geen toegang heeft tot gegevens, geeft u de SCHEMABINDING optie op om te voorkomen dat de query optimizer onnodige spooloperators genereert voor queryplannen met betrekking tot deze UDF's. Zie De referentie voor logische en fysieke showplanoperatoren voor meer informatie over spools. Zie Schemagebonden functiesvoor meer informatie over het maken van een schemagebonden functie.

Deelnemen aan een MSTVF in een FROM clausule is mogelijk, maar kan leiden tot slechte prestaties. SQL Server kan niet alle geoptimaliseerde technieken gebruiken voor sommige instructies die kunnen worden opgenomen in een MSTVF, wat resulteert in een suboptimaal queryplan. Om de best mogelijke prestaties te verkrijgen, gebruikt u waar mogelijk joins tussen basistabellen in plaats van functies.

MSTVF's hebben een vaste kardinaliteits schatting van 100 vanaf SQL Server 2014 (12.x) en 1 voor eerdere versies van SQL Server.

In SQL Server 2017 (14.x) en latere versies kan het optimaliseren van een uitvoeringsplan dat gebruikmaakt van MSTVF's interleaved uitvoering toepassen, wat resulteert in het gebruik van de werkelijke kardinaliteit in plaats van de eerder genoemde heuristieken.

Zie Interleaved-uitvoering voor tabelfuncties met meerdere instructiesvoor meer informatie.

ANSI_WARNINGS wordt niet gehonoreerd wanneer u parameters doorgeeft in een opgeslagen procedure, door de gebruiker gedefinieerde functie of wanneer u variabelen declareert en instelt in een batch-instructie. Als een variabele bijvoorbeeld is gedefinieerd als teken(3)en vervolgens is ingesteld op een waarde die groter is dan drie tekens, worden de gegevens afgekapt tot de gedefinieerde grootte en slaagt de instructie INSERT of UPDATE.

Feedback

Is deze pagina nuttig?