Tipi di funzioni

Funzioni scalari

Le funzioni scalari definite dall'utente restituiscono un singolo valore di dati del tipo definito nella clausola RETURNS. Per le funzioni scalari semplici non è disponibile alcun corpo della funzione. Il valore scalare corrisponde al risultato di una singola istruzione FUNCTION, in genere un'istruzione SELECT. Nelle funzioni scalari con istruzioni multiple il corpo della funzione è definito in un blocco BEGIN...END e include una serie di istruzioni Transact-SQL che restituiscono un singolo valore. Il tipo restituito può essere qualsiasi tipo di dati ad eccezione di text, ntext, image, cursor, spatial, hierarchyID e timestamp.

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

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

Negli esempi seguenti viene creata una funzione scalare con istruzioni multiple. La funzione accetta un valore di input, un valore ProductID e restituisce la quantità aggregata del prodotto specificato nelle scorte come singolo valore restituito.

USE AdventureWorks;
GO
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;
GO

Funzioni con valori di tabella

Le funzioni con valori di tabella definite dall'utente restituiscono un tipo di dati table. Per una funzione inline con valori di tabella non è disponibile alcun corpo della funzione. La tabella corrisponde al set di risultati di una singola istruzione SELECT.

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

USE AdventureWorks;
GO
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 'YTD 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
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

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

SELECT * FROM Sales.ufn_SalesByStore (602);

In una funzione con valori di tabella con istruzioni multiple il blocco BEGIN…END definisce il corpo della funzione e contiene una serie di istruzioni Transact-SQL che creano e inseriscono righe nella tabella dei risultati.

Nell'esempio seguente viene creata una funzione con valori di tabella. La funzione accetta il solo parametro di input EmployeeID e restituisce un elenco di tutti i dipendenti che fanno riferimento direttamente o indirettamente al dipendente specificato con ID 109. Il dipendente con ID 109 viene quindi utilizzato come parametro di input nell'esempio e nella tabella dei risultati viene restituito un elenco di dipendenti.

USE AdventureWorks;
GO
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,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

Funzioni predefinite

Le funzioni predefinite di SQL Server consentono di eseguire un'ampia gamma di operazioni. Tali funzioni non possono essere modificate. È possibile utilizzare le funzioni predefinite in istruzioni Transact-SQL per effettuare le operazioni seguenti:

• Accedere alle informazioni dalle tabelle di sistema SQL Server senza accedere direttamente alle tabelle di sistema. Per ulteriori informazioni, vedere Utilizzo delle funzioni di sistema.

• Eseguire operazioni comuni come SUM, GETDATE o IDENTITY. Per ulteriori informazioni, vedere Funzioni (Transact-SQL).

Le funzioni predefinite restituiscono tipi di dati scalari o table. La funzione @@ERROR, ad esempio, restituisce 0 se l'ultima istruzione Transact-SQL è stata eseguita correttamente. Se l'istruzione ha generato un errore, la funzione @@ERROR restituirà il numero di errore. La funzione SUM(parameter) restituisce la somma di tutti i valori per il parametro.

Cronologia modifiche

Vedere anche

Riferimento

Altre risorse