Skapa användardefinierade funktioner (databasmotor)

Artikel
2025-03-05

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 och restriktioner

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 inte TRY...CATCH, @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.
Satsen FOR XML tillåts inte.
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.
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

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

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

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

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. Mer information om spolar finns i Showplan Referens för logiska och fysiska operatorer. 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 SQL Server-versioner.

Från och med SQL Server 2017 (14.x) kan optimering av en körningsplan som använder MSTVF:er använda sammanflätad körning, vilket gör det möjligt att använda faktisk kardinalitet istället för de tidigare nämnda heuristiken.

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.

Dela via