Skapa användardefinierade funktioner (databasmotor)

2025-07-18

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Den här artikeln beskriver hur du skapar en användardefinierad funktion (UDF) i SQL Server med hjälp av Transact-SQL.

Begränsningar

Användardefinierade funktioner kan inte användas för att utföra åtgärder som ändrar databastillståndet.

Användardefinierade funktioner får inte innehålla en OUTPUT INTO-sats som har en tabell som mål.

Användardefinierade funktioner kan inte returnera flera resultatuppsättningar. Använd en lagrad procedur om du behöver returnera flera resultatuppsättningar.

Felhanteringen är begränsad i en användardefinierad funktion. En UDF stöder TRY...CATCH inte, @ERROR eller RAISERROR.

Användardefinierade funktioner kan inte anropa en lagrad procedur, men kan anropa en utökad lagrad procedur.

Användardefinierade funktioner kan inte använda dynamiska SQL- eller temp-tabeller. Tabellvariabler tillåts.

SET -instruktioner tillåts inte i en användardefinierad funktion (till exempel SET NOCOUNT ON;). Tilldelning av variabelvärde kan använda SET.

Satsen FOR XML tillåts inte.

Kapslade användardefinierade funktioner

Användardefinierade funktioner kan kapslas. Det vill säga en användardefinierad funktion kan anropa en annan. Kapslingsnivån ökas när den anropade funktionen börjar att köras och minskas när den anropade funktionen slutar att köra klart.

Användardefinierade funktioner kan kapslas upp till 32 nivåer. Om du överskrider de maximala kapslingsnivåerna orsakar det att hela kedjan av anropsfunktioner misslyckas. Alla referenser till hanterad kod från en Transact-SQL-användardefinierad funktion räknas som ett nivå mot gränsen på 32 kapslingsnivåer.

Metoder som anropas inifrån hanterad kod räknas inte mot den här gränsen.

Service Broker-uttalanden

Följande Service Broker-instruktioner kan inte inkluderas i definitionen av en Transact-SQL användardefinierad funktion:

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

Sidoeffektfunktioner

Följande icke-terministiska inbyggda funktioner kan inte användas i en Transact-SQL användardefinierad funktion (UDF).

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Om du refererar till någon av dessa funktioner i en UDF får du följande fel:

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

En lista över deterministiska och nondeterministiska inbyggda systemfunktioner finns i Deterministiska och nondeterministiska funktioner.

Om du vill undvika det här problemet kan du omsluta sidoeffektfunktionen i en vy och anropa vyn inifrån en funktion.

Behörigheter

Kräver CREATE FUNCTION behörighet i databasen och ALTER behörighet för schemat där funktionen skapas. Om funktionen anger en användardefinierad typ kräver EXECUTE behörighet för typen.

Exempel på skalär funktion

Skalär funktion (skalär UDF)

I följande exempel skapas en skalär funktion (skalär UDF) i databasen AdventureWorks2022. Funktionen tar ett indatavärde, ett ProductIDoch returnerar ett enda datavärde, den aggregerade kvantiteten för den angivna produkten i lagret.

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

I följande exempel används funktionen ufnGetInventoryStock för att returnera den aktuella lagerkvantiteten för produkter som har en ProductModelID mellan 75 och 80.

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

Mer information och exempel på skalära funktioner finns i CREATE FUNCTION.

Exempel på tabellvärdesfunktioner

Inlinje tabellvärdesfunktion (TVF)

I följande exempel skapas en infogad tabellvärdesfunktion (TVF) i databasen AdventureWorks2022. Funktionen tar en indataparameter, ett kund-ID (store) och returnerar kolumnerna ProductID, Nameoch summan av hittills föregående års försäljning som YTD Total för varje produkt som säljs till butiken.

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

I följande exempel anropas funktionen och kund-ID 602 anges.

SELECT *
FROM Sales.ufn_SalesByStore(602);

Tabellvärdesfunktion med flera instruktioner (MSTVF)

I följande exempel skapas en tabellvärdesfunktion med flera satser (MSTVF) i databasen AdventureWorks2022. Funktionen tar en enskild indataparameter, en EmployeeID och returnerar en lista över alla anställda som rapporterar till den angivna medarbetaren direkt eller indirekt. Funktionen anropas sedan och anger medarbetar-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

I det följande exemplet anropas funktionen och specificeras medarbetar-ID 1.

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

Mer information och exempel på infogade tabellvärdesfunktioner (infogade TVF:er) och MSTVF-funktioner (multi-statement table-valued functions) finns i CREATE FUNCTION.

Metodtips

Om en användardefinierad funktion (UDF) inte skapas med satsen SCHEMABINDING kan ändringar som görs i underliggande objekt påverka definitionen av funktionen och ge oväntade resultat när den anropas. Vi rekommenderar att du implementerar någon av följande metoder för att säkerställa att funktionen inte blir inaktuell på grund av ändringar i dess underliggande objekt:

Ange WITH SCHEMABINDING-satsen när du skapar UDF. Detta säkerställer att objekten som refereras i funktionsdefinitionen inte kan ändras om inte funktionen också ändras.
Kör den sp_refreshsqlmodule lagrade proceduren när du har modifierat alla objekt som anges i definitionen av UDF.

Om du skapar en UDF som inte har åtkomst till data anger du alternativet SCHEMABINDING för att förhindra att frågeoptimeraren genererar onödiga spooloperatorer för frågeplaner som involverar dessa UDF:er. För mer information om spools, se Operatorreferens för logisk och fysisk showplan. Mer information om hur du skapar en schemabunden funktion finns i Schemabundna funktioner.

Det är möjligt att ansluta sig till en MSTVF i en FROM-sats, men det kan leda till dålig prestanda. SQL Server kan inte använda alla optimerade tekniker på vissa instruktioner som kan ingå i en MSTVF, vilket resulterar i en suboptimal frågeplan. För att få bästa möjliga prestanda kan du när det är möjligt använda kopplingar mellan bastabeller i stället för funktioner.

MSTVFs har en fast kardinalitets gissning på 100 från och med SQL Server 2014 (12.x) och 1 för tidigare versioner av SQL Server.

I SQL Server 2017 (14.x) och senare versioner kan optimering av en exekveringsplan som använder MSTVF:er dra nytta av interleaved execution, vilket leder till att man använder faktisk kardinalitet i stället för de tidigare nämnda heuristikerna.

Mer information finns i Interleaved execution for multi-statement table valued functions.

ANSI_WARNINGS respekteras inte när du skickar parametrar i en lagrad procedur, användardefinierad funktion eller när du deklarerar och anger variabler i en batch-instruktion. Om en variabel definieras som tecken(3)och sedan tilldelas ett värde som är större än tre tecken, förkortas data till den definierade storleken och INSERT- eller UPDATE-instruktionen utförs framgångsrikt.

Feedback

Var den här sidan till hjälp?