Funktionstypen

Skalarfunktionen

Benutzerdefinierte Skalarfunktionen geben einen einzelnen Datenwert des definierten Datentyps in einer RETURNS-Klausel zurück. Einfache Skalarfunktionen haben keinen Funktionshauptteil. Der Skalarwert ergibt sich aus einer einzelnen Funktionsanweisung (häufig eine SELECT-Anweisung). Bei aus mehreren Anweisungen bestehenden Skalarfunktionen enthält der in einem BEGIN…END-Block definierte Funktionshauptteil eine Reihe von Transact-SQL-Anweisungen, die einen einzelnen Wert zurückgeben. Der Rückgabetyp kann ein beliebiger Datentyp mit Ausnahme von text, ntext, image, cursor, spatial, hierarchyID und timestamp sein.

Im folgenden Beispiel wird die einzelne ufnGetInventoryStock-Skalarfunktion verwendet, um den aktuellen Lagerbestand für Produkte mit einer ProductModelID zwischen 75 und 80 zurückzugeben.

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

Im folgenden Beispiel wird eine Skalarfunktion mit mehreren Anweisungen erstellt. Die Funktion nimmt einen Eingabewert an (ProductID) und gibt einen einzelnen Datenwert zurück, der die aggregierte Menge des Lagerbestands für das angegebene Produkt darstellt.

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

Tabellenwertfunktionen

Benutzerdefinierte Tabellenwertfunktionen geben einen table-Datentyp zurück. Bei einer Inlinefunktion mit Tabellenrückgabe gibt es keinen Funktionshauptteil; die Tabelle ist das Resultset einer einzelnen SELECT-Anweisung.

Das folgende Beispiel erstellt eine Inline-Tabellenwertfunktion. Die Funktion nimmt einen Eingabeparameter (eine Kunden-ID (Geschäfts-ID)) an und gibt die Spalten ProductID, Name sowie das Aggregat der bisherigen Verkaufseinnahmen dieses Jahres als YTD Total für jedes Produkt zurück, das an das Geschäft verkauft wurde.

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

Das folgende Beispiel ruft die Funktion auf und gibt die Kunden-ID 602 an.

SELECT * FROM Sales.ufn_SalesByStore (602);

Bei einer aus mehreren Anweisungen bestehenden Tabellenwertfunktion enthält der in einem BEGIN…END-Block definierte Funktionshauptteil eine Reihe von Transact-SQL-Anweisungen, die Zeilen erstellen und in die Tabellenergebnisse einfügen.

Das folgende Beispiel erstellt eine Tabellenwertfunktion. Die Funktion nimmt den einzelnen Eingabeparameter an (EmployeeID) und gibt eine Liste aller Mitarbeiter zurück, die dem angegebenen Mitarbeiter mit der Mitarbeiter-ID 109 direkt oder indirekt unterstellt sind. Die Mitarbeiter-ID 109 wird dann im Beispiel als Eingabeparameter verwendet, und eine Liste der Mitarbeiter wird in der Ergebnistabelle zurückgegeben.

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

Integrierte Funktionen

Integrierte Funktionen werden von SQL Server bereitgestellt, um Sie beim Ausführen einer Vielzahl von Operationen zu unterstützen. Sie können nicht geändert werden. Sie können integrierte Funktionen in Transact-SQL-Anweisungen für die folgenden Aufgaben verwenden:

• Zugreifen auf Informationen aus SQL Server-Systemtabellen, ohne direkt auf die Systemtabellen zugreifen zu müssen. Weitere Informationen finden Sie unter Verwenden von Systemfunktionen.

• Ausführen allgemeiner Aufgaben, z. B. SUM, GETDATE oder IDENTITY. Weitere Informationen finden Sie unter Funktionen (Transact-SQL).

Integrierte Funktionen geben Skalar- oder table-Datentypen zurück. @@ERROR gibt z. B. 0 zurück, nachdem die letzte Transact-SQL-Anweisung erfolgreich ausgeführt wurde. Wenn die Anweisung einen Fehler generiert hat, gibt @@ERROR die Fehlernummer zurück. Die Funktion SUM(parameter) gibt die Summe aller Werte für den Parameter zurück.

Änderungsverlauf

Siehe auch

Verweis

Andere Ressourcen