Benutzerdefinierte Funktionen im MS SQL Server

“Die Drei von der Tankstelle”, “Drei Engel für Charlie” und “Die drei Fragezeichen” haben eines gemeinsam: Es sind immer Drei. Drei, von denen jeder seine besonderen Fähigkeiten hat, und Drei, die sich perfekt ergänzen. So ist es auch beim SQL Server. Neben den gespeicherten Prozeduren und Triggern bietet der SQL Server mit den benutzerdefinierten Funktionen eine dritte Komponente, mit der Programmlogik gespeichert und wiederverwendet werden kann. Und weil aller guten Dinge nun mal drei sind, gibt es die benutzerdefinierten Funktionen auch gleich in drei verschiedenen Typen.

Diese drei verschiedenen Typen heißen Inlinefunktionen, Tabellenfunktionen und Skalarfunktionen.

Skalarfunktionen sind weitestgehend mit VBA-Funktionen in Access vergleichbar. Anhand einer optionalen Parameterübergabe wird ein Wert ermittelt und zurückgegeben. Die Inline- und Tabellenfunktionen gehen dort noch einen Schritt weiter, denn sie liefern nicht nur einen Wert, sondern komplette Ergebnismengen.

Die ähnlichkeit zu VBA-Funktionen liegt aber nicht nur in der Ermittlung der Daten, sondern auch in ihrer Anwendung. Gerade die Verwendung eigener geschriebener Funktionen in SELECT-Anweisungen, Access-Abfragen, Gültigkeitsregeln und Modulen ist einer der Vorteile von Access. Dieser Vorteil gilt auch für benutzerdefinierte Funktionen, denn diese sind in der SQL Server-Entwicklung universell einsetzbar. Sie können in SELECT-Anweisungen, in Sichten, in gespeicherten Prozeduren, in Triggern und in anderen benutzerdefinierten Funktionen wie auch in Standardwerten und Einschränkungen verwendet werden und stehen somit für die Realisierung verschiedenster Anforderungen zur Verfügung.

Einsatzmöglichkeiten

Der Grund für den Einsatz von benutzerdefinierten Funktionen ist derselbe wie bei gespeicherten Prozeduren: Die Kapselung von Programmlogik und somit die Kapselung von Geschäftsregeln und von Regeln zur Datenkonsistenz.

Zwar können Sie mit benutzerdefinierten Funktionen keine Daten manipulieren, aber im Vergleich zu gespeicherten Prozeduren sind benutzerdefinierte Funktionen flexibler einsetzbar. Im Gegensatz zu gespeicherten Prozeduren können diese nämlich auch in Sichten, SELECT-Anweisungen oder in anderen benutzerdefinierten Funktionen verwendet werden. Insofern ist der Einsatz benutzerdefinierter Funktionen in manchen Fällen sinnvoller als der Einsatz gespeicherter Prozeduren.

Für die beispielsweise oft benötigte Ermittlung der Produktbezeichnung anhand einer Produktnummer können Sie eine Skalarfunktion erstellen, die immer genau den einen Wert – die Produktbezeichnung – anhand der übergebenen Produktnummer liefert.

Eine andere Skalarfunktion könnte die in der Northwind-Datenbank immer wieder notwendige Summe einer Bestellposition anhand der Spalten Einzelpreis, Menge und Discount berechnen.

Die Werte solcher Skalarfunktionen können Sie als Spalte in einer SELECT-Anweisung, als Filterkriterium in einer WHERE-Bedingung oder in einer INSERT– oder UPDATE-Anweisung verwenden.

In SELECT-Anweisungen und bei Datenmanipulationen stehen auch die Tabellen- und Inlinefunktionen zur Verfügung. Allerdings liefern diese Typen nicht einzelne Werte, sondern Ergebnismengen und sind somit ein guter Ersatz für Sichten oder Tabellen.

Tabellen- und Inlinefunktionen bieten gerade Sichten gegenüber einen großen Vorteil: Sie unterstützen Parameter.

Eine Sicht liefert immer alle Daten der in der Sicht angegebenen SQL-Anweisung. Die Datenermittlung ist starr und kann nicht durch Parameter beeinflusst werden. In Tabellen- und Inlinefunktionen hingegen sind Parameter erlaubt.

Dadurch können die Daten bereits bei der Ermittlung anhand der übergebenen Parameterwerte gefiltert werden.

Es ist schon ein Unterschied, ob in einer Tabellenverknüpfung von Bestellkopf und Bestellpositionen alle Bestellpositionen verknüpft werden oder dabei nur die Bestellpositionen berücksichtigt werden, die dem Wert eines übergebenen Parameters entsprechen.

Sie sehen, die Einsatzmöglichkeiten sind so vielfältig wie die von VBA-Funktionen in Access. Die Verwendung benutzerdefinierter Funktionen ist für Sie insofern eigentlich nichts Neues. Bis auf die Entwicklung dieser Funktionen, denn die schreiben Sie natürlich nicht in VBA, sondern in T-SQL. Dabei sind Art und Umfang der Entwicklungsmöglichkeit abhängig vom jeweiligen Typ.

Inline- und Tabellenfunktionen unterscheiden sich in der Werterückgabe gegenüber den Skalarfunktionen und die Skalar- und Tabellenfunktionen unterscheiden sich anhand der Möglichkeiten zur Ermittlung des Werts beziehungsweise der Werte von den Inlinefunktionen.

Inlinefunktionen

Inlinefunktionen liefern eine Ergebnismenge und können insofern wie Tabellen und Sichten – und somit fast überall – verwendet werden. Aber nur fast, denn bei der Ermittlung von Standardwerten oder der Einhaltung von Gültigkeitsregeln wird dieser Funktionstyp nicht unterstützt.

Der große Vorteil einer Inlinefunktion liegt darin, dass bei der Ermittlung der Daten übergebene Parameterwerte als Filterkriterien verwendet werden können. Damit ist eigentlich auch bereits die Definition einer Inlinefunktion beschrieben. Sie besteht aus einer einzigen SELECT-Anweisung, die anhand eines Parameterwerts gefiltert werden kann. Mehr außer dieser gibt es dort nicht. Von der Programmierung her ist die Inlinefunktion daher die einfachste Variante.

Eine neue Inlinefunktion können Sie mit dem SQL Server Management Studio anlegen. Sie finden die benutzerdefinierten Funktionen unter Programmierbarkeit innerhalb der jeweiligen Datenbank. Dort sehen Sie unter Gespeicherte Prozeduren auch den Knoten Funktionen, der Ihnen nach einem Klick auf den Knoten weitere Verzweigungen anzeigt.

Inlinefunktionen liefern eine Ergebnismenge über eine virtuelle Tabelle und gehören somit zur Gruppe der Tabellenwertfunktionen.

Durch einen Klick mit der rechten Maustaste auf den Knoten Tabellenwertfunktionen können Sie den Befehl Neue Inline-Tabellenwertfunktion (siehe Bild 1) auswählen. Sie erhalten auch hier – wie bei den gespeicherten Prozeduren und Triggern – ein neues Abfragefenster mit einem Vorlageskript.

Bild 1: Eine neue Inlinefunktion

Die Definition einer neuen Funktion beginnt mit dem Befehl CREATE FUNCTION, gefolgt vom Funktionsnamen, der immer das entsprechende Schema enthalten muss. Wie bereits bei den gespeicherten Prozeduren beschrieben, werden über das Schema eines Objekts unter anderem die Zugriffsrechte verwaltet. Sofern Sie kein detailliertes Berechtigungskonzept einsetzen, ist das Schema in der Regel dbo. Sie sollten für eine einheitliche Basis für die Weiterentwicklung und eine spätere Konfiguration eines Berechtigungskonzepts immer dbo angeben.

Eine genauere Erklärung der Berechtigungsvergabe anhand der Schemata würde allerdings den Rahmen dieses Artikels sprengen.

Der oder die Parameter, die in der SELECT-Anweisung der Inlinefunktion verwendet werden, geben Sie durch Komma getrennt in Klammern hinter dem Namen der Funktion an. Dabei verwenden Sie das @-Zeichen zur Kennzeichnung jedes einzelnen Parameters sowie den Datentyp.

Nach den Parametern definieren Sie mit RETURNS TABLE, welchen Datentyp das Ergebnis der Funktion aufweist. Eine Inlinefunktion liefert eine Ergebnismenge anhand einer virtuellen Tabelle über den Datentyp TABLE. Die Struktur dieser virtuellen Tabelle ergibt sich aus der SELECT-Anweisung. Nach RETURNS TABLE leiten Sie mit AS den eigentlichen Programmcode ein, der in Klammern angegeben werden muss. Der Programmcode besteht wie bereits erwähnt nur aus einer einzelnen SELECT-Anweisung. Eine weitere Spezifikation zur Kennzeichnung der Tabellenfunktion als Inlinefunktion ist nicht notwendig.

Als Beispiel sehen Sie in Listing 1 die Inlinefunktion dbo.ifArtikelEinerKategorie, die alle aktiven oder inaktiven Artikel einer bestimmten Kategorie als Ergebnismenge liefert.

Listing 1: Beispiel für eine Inlinefunktion

CREATE FUNCTION dbo.ifArtikelEinerKategorie 
( 
 @CategoryId int,
 @Discontinued bit
)
RETURNS TABLE 
AS
RETURN 
(
 SELECT 
 dbo.Products.ProductId, dbo.Products.ProductName, 
 dbo.Products.SupplierID, dbo.Suppliers.CompanyName, 
 dbo.Products.CategoryID, dbo.Categories.CategoryName, 
 dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, 
 dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, 
 dbo.Products.ReorderLevel, dbo.Products.Discontinued
 FROM 
 dbo.Products 
 INNER JOIN dbo.Categories 
 ON dbo.Products.CategoryID = dbo.Categories.CategoryID 
 INNER JOIN dbo.Suppliers 
 ON Products.SupplierID = Suppliers.SupplierID
 WHERE
 dbo.Products.CategoryId = @CategoryId
 AND
 dbo.Products.Discontinued = @Discontinued
)

Die Erklärung dieser Inlinefunktion ist recht einfach. Die SELECT-Anweisung ermittelt alle Artikeldaten zuzüglich der Bezeichnung der Kategorie, zu der die Artikel gehören, sowie den Firmennamen des Lieferanten. Diese Ergebnismenge wird bei der Ermittlung auf eine Kategorie gefiltert, die über den Parameter @CategoryId an die SELECT-Anweisung übergeben wird. Zusätzlich wird über den Parameter @Discontinued bestimmt, ob die Ergebnismenge nur die aktiven Artikel (Wert 0) oder nur die inaktiven Artikel (Wert 1) enthält.

Bei der SELECT-Anweisung müssen Sie darauf achten, dass alle Spalten einen Namen bekommen. Dies gilt insbesondere bei der Verwendung von Konstanten oder berechneten Spalten. Hier muss zwingend ein Alias vergeben werden. Diese Inlinefunktion können Sie nun wie eine “parametrisierte Sicht” verwenden. Folgender Aufruf liefert alle aktiven Artikel der Kategorie 1:

SELECT * FROM dbo.ifArtikelEinerKategorie (1, 0)

Sie können die Inlinefunktion auch in einer Tabellenverknüpfung verwenden. Die folgende Abfrage etwa liefert alle Bestellpositionen, die inaktive Artikel der Kategorie 1 beinhalten:

SELECT * FROM dbo.[Order Details] OD
INNER JOIN dbo.ifArtikelEinerKategorie(1, 1) AEK
ON OD.ProductId = AEK.ProductId

Für einfache SELECT-Anweisungen ist die Inlinefunktion die erste Wahl, da sie schnell programmiert und flexibel einsetzbar ist. Flexibel einsetzbar sind auch die Tabellenfunktionen – und sie sind auch flexibel in der Ermittlung der Ergebnismenge.

Tabellenfunktionen

Im Unterschied zu einer Inlinefunktion können in einer Tabellenfunktion mehrere SQL-Anweisungen zur Ermittlung der Ergebnismenge gespeichert werden.

Dies sagt bereits der Kontextmenübefehl des Knotens Tabellenwertfunktionen aus: Neue Tabellenwertfunktion mit mehreren Anweisungen (siehe Bild 1). Auch dieser Befehl liefert Ihnen eine Skriptvorlage in einem neuen Abfragefenster. Diese unterscheidet sich in einigen Punkten von der der Inlinefunktion.

Bei einer Tabellenfunktion wird die Struktur der virtuellen Tabelle nicht anhand einer SELECT-Anweisung bestimmt, sondern muss durch eine TABLE-Variable explizit definiert werden. Insofern gibt es hier kein RETURNS TABLE, sondern ein RETURNS gefolgt von der Definition der TABLE-Variablen.

Die einzelnen SQL-Anweisungen zur Ermittlung der Ergebnismenge werden in einer Tabellenfunktion im BEGIN…END-Block zusammengefasst. Mit diesen SQL-Anweisungen ermitteln Sie nach und nach die Daten und füllen mit diesen die TABLE-Variable. Dabei stehen Ihnen alle Möglichkeiten der T-SQL-Programmierung zur Verfügung, bis auf wenige Ausnahmen:

• Ausgabe von Meldungen: Benutzerdefinierte Funktionen liefern immer Werte oder Ergebnismengen. Meldungen, die zum Beispiel auf einen Fehler hinweisen und durch RAISERROR oder PRINT ausgelöst werden, werden nicht unterstützt.
• Datenmanipulationen außerhalb der Funktion: Innerhalb der Tabellenfunktion können Sie die Daten der dort enthaltenen Objekte – sprich temporären Tabellen oder Table-Variablen – manipulieren; die Daten anderer Tabellen jedoch nicht.
• Verwendung von gespeicherten Prozeduren: Gespeicherte Prozeduren können in benutzerdefinierten Funktionen nicht aufgerufen werden. Der Befehl EXECUTE wird nicht unterstützt. Dies würde auch der gerade erwähnten Einschränkung widersprechen, dass in benutzerdefinierten Funktionen keine Daten von Tabellen manipuliert werden können. Wären in benutzerdefinierten Funktionen gespeicherte Prozeduren erlaubt, wären über diese die Datenmanipulationen möglich.

Username or Email

Passwort

Angemeldet bleiben