Creare funzioni definite dall'utente (motore di database)

2025-07-18

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

In questo articolo viene descritto come creare una funzione definita dall'utente in SSQL Server usando Transact-SQL.

Limitazioni

Non è possibile utilizzare funzioni definite dall'utente per eseguire azioni che modificano lo stato del database.

Le funzioni definite dall'utente non possono contenere una clausola OUTPUT INTO che ha una tabella come destinazione.

Le funzioni definite dall'utente non possono restituire più set di risultati. Utilizzare una stored procedure se è necessario restituire più set di risultati.

In una funzione definita dall'utente la gestione degli errori è limitata. Una funzione definita dall'utente non supporta TRY...CATCH, @ERROR o RAISERROR.

Tramite le funzioni definite dall'utente non è possibile chiamare una stored procedure normale, bensì una estesa.

Nelle funzioni definite dall'utente non è possibile utilizzare tabelle temporanee o SQL dinamiche. Sono consentite le variabili di tabella.

SET Le istruzioni non sono consentite in una funzione definita dall'utente , ad esempio SET NOCOUNT ON;. L'assegnazione di valori variabile può usare SET.

La clausola FOR XML non è consentita.

Funzioni annidate definite dall'utente

Le funzioni definite dall'utente possono essere annidate. Ovvero, una funzione definita dall'utente può chiamare un'altra. Il livello di nidificazione aumenta all'avvio della funzione richiamata e diminuisce al termine dell'esecuzione della funzione.

Le funzioni definite dall'utente possono essere nidificate fino a un massimo di 32 livelli. Se viene superato il livello massimo di nidificazioni, l'intera sequenza di funzioni chiamanti ha esito negativo. Qualsiasi riferimento al codice gestito presente in una funzione definita dall'utente Transact-SQL viene considerato come un livello nel contesto del limite di 32 livelli di nidificazione.

I metodi richiamati dal codice gestito non vengono inclusi nel conteggio per questo limite.

Istruzioni del Service Broker

Nella definizione di una funzione definita dall'utente Transact-SQL non è possibile includere le istruzioni di Service Broker seguenti:

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

Funzioni di effetto collaterale

Le funzioni predefinite non deterministiche seguenti non possono essere usate in una funzione definita dall'utente Transact-SQL.

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Se si fa riferimento a una di queste funzioni all'interno di una UDF, si riceve il seguente errore:

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

Per un elenco delle funzioni di sistema predefinite deterministiche e non deterministiche, vedere Funzioni deterministiche e non deterministiche.

Per risolvere questo problema, è possibile incapsulare la funzione di effetto collaterale in una vista e chiamare la vista dall'interno di una funzione.

Autorizzazioni

È necessario disporre dell'autorizzazione CREATE FUNCTION nel database e dell'autorizzazione ALTER per lo schema in cui la funzione è in fase di creazione. Se per la funzione viene specificato un tipo definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per tale tipo.

Esempi di funzioni scalari

Funzione scalare (funzione definita dall'utente scalare)

Nell'esempio seguente viene creata una funzione scalare (UDF scalare) con istruzioni multiple nel database AdventureWorks2022. La funzione accetta un valore di input, un valore ProductIDe restituisce un singolo valore di dati, la quantità aggregata del prodotto specificato nelle scorte.

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

Nell'esempio seguente viene utilizzata la funzione ufnGetInventoryStock per conoscere la quantità di scorte corrente dei prodotti il cui valore ProductModelID è compreso tra 75 e 80.

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

Per altre informazioni ed esempi di funzioni scalari, vedere CREATE FUNCTION.

Funzione con valori di tabella di esempio

Funzioni con valori di tabella inline (TVF)

Nell'esempio seguente viene creata una funzione con valori di tabella inline con valori di tabella nel database AdventureWorks2022. La funzione accetta un parametro di input, un ID (punto vendita) cliente e restituisce le colonne ProductID, Namee l'aggregazione delle vendite per l'anno in corso come valore YTD Total per ogni prodotto venduto al punto vendita.

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

Nell'esempio seguente viene richiamata la funzione e viene specificato l'ID cliente 602.

SELECT *
FROM Sales.ufn_SalesByStore(602);

Funzioni con valori di tabella con istruzioni multiple (MSTVF)

Nell'esempio seguente viene creata una funzione con valori di tabella con istruzioni multiple nel database AdventureWorks2022. Nella funzione viene accettato un solo parametro di input, un valore EmployeeID , e tramite essa viene restituito un elenco di tutti i dipendenti che fanno riferimento direttamente o indirettamente al dipendente specificato. La funzione viene quindi richiamata specificando l'ID dipendente 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

Nell'esempio seguente viene richiamata la funzione e viene specificato l'ID dipendente 1.

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

Per altre informazioni ed esempi sulle funzioni inline con valori di tabella e sulle funzioni con valori di tabella con istruzioni multiple, vedere CREATE FUNCTION.

Procedure consigliate

Se una funzione definita dall'utente non viene creata tramite la clausola SCHEMABINDING, le modifiche apportate agli oggetti sottostanti possono influire sulla definizione della funzione e generare risultati imprevisti quando viene richiamata. È consigliabile implementare uno dei metodi seguenti per assicurarsi che la funzione non diventi obsoleta in seguito a modifiche degli oggetti sottostanti:

Specificare la clausola WITH SCHEMABINDING quando si crea la funzione definita dall'utente. In questo modo, gli oggetti a cui si fa riferimento nella definizione della funzione possono essere modificati solo se viene modificata anche la funzione.
Eseguire la stored procedure sp_refreshsqlmodule dopo avere modificato qualsiasi oggetto specificato nella definizione della funzione definita dall'utente.

Se si crea una UDF che non accede ai dati, specificare l'opzione SCHEMABINDING per impedire a Query Optimizer di generare operatori di spooling non necessari per i piani di query che coinvolgono queste UDF. Per altre informazioni sugli spool, vedere Informazioni di riferimento sugli operatori showplan logici e fisici. Per altre informazioni sulla creazione di una funzione associata a uno schema, vedere Funzioni associate a schema.

La creazione di un join di una funzione con valori di tabella con istruzioni multiple in una clausola FROM è possibile, ma può compromettere le prestazioni. In SQL Server non è possibile usare tutte le tecniche ottimizzate con alcune istruzioni che possono essere incluse in una funzione con valori di tabella con istruzioni multiple, comportando un piano di query non ottimale. Per ottenere le migliori prestazioni possibili, utilizzare i join tra le tabelle di base anziché tra le funzioni, quando possibile.

Le MSTVFs hanno una cardinalità fissa di 100 a partire da SQL Server 2014 (12.x) e di 1 per le versioni precedenti di SQL Server.

In SQL Server 2017 (14.x) e versioni successive, l'ottimizzazione di un piano di esecuzione che usa le funzioni a valori di tabella a più istruzioni (MSTVF) può usare l'esecuzione intercalata, che implica l'uso della cardinalità effettiva anziché l'euristica menzionata in precedenza.

Per altre informazioni, vedere Esecuzione interleaved per funzioni con valori di tabella a più istruzioni.

ANSI_WARNINGS non viene applicata quando vengono passati parametri a una stored procedure, una funzione definita dall'utente oppure in caso di dichiarazione e impostazione delle variabili in un'istruzione batch. Se, ad esempio, la variabile viene definita come char(3) e quindi impostata su un valore maggiore di tre caratteri, i dati verranno troncati alle dimensioni definite e l'istruzione INSERT o UPDATE avrà esito positivo.