Kronester

Ich möchte heute eine interessante Methode vorstellen die es ermöglicht die Ergebnisse von Funktionen in SQL-Abfragen zu verwenden.

Die sogenannten Table-Values-Functions (Funktionen die eine Tabelle als Return-Wert haben) können bei Abfragen wie eine Tabelle in ein Select-Statement eingebunden werden. Parameterwerte dürfen jedoch bei einem normalen Join nur von außerhalb des Select-Statements versorgt werden.

Als Beispiel könnte die in der AdventureWorks enthaltene Funktion dbo.ufnGetContactInformation in einer Abfrage verwendet werden. Als Parameterwert kann die ContactID einer Person übergeben werden und die TVF (Table-Valued-Function) liefert ein paar Kontaktdaten (Name, Vorname, Position, KontaktArt) zurück.

Möchte man diese Funktionalität nun in einem Select verwenden, ist man versucht die TVF, die ja wie eine Tabelle behandelt werden kann, wie folgt einzusetzen:

Nicht Korrekt!

 T-SQL | 
 copy code |
?
 
01
WITH Liste AS
02
(
03
    SELECT   SalesPersonID
04
            ,SUM(TotalDue) AS TotalDue
05
    FROM Sales.SalesOrderHeader
06
    GROUP BY  SalesPersonID
07
)
08
SELECT   ci.FirstName
09
        ,ci.LastName
10
        ,ci.JobTitle
11
        ,ci.ContactType
12
        , l.TotalDue
13
FROM Liste l
14
join HumanResources.Employee e 
15
      ON e.EmployeeID = l.SalesPersonID
16
join dbo.ufnGetContactInformation(e.ContactID) ci 
17
      ON ci.ContactID = e.ContactID

In dem obigen Beispiel wird man eine Fehlermeldung bekommen, da hier versucht wird einen Parameterwert aus dem laufenden Select zu verwenden bzw. die TVF als JOIN-Tabelle genutzt wird.

Um eine TVF zu einem Select-Bestand zu joinen, muss stattdessen die Variante mittels APPLY gewählt werden. Hier gibt es zwei Möglichkeiten die einem INNER bzw. OUTER JOIN entsprechen.

Mittels CROSS APPLY wird das Ergebnis der TVF an das bestehende Resultset angehängt. Das Verhalten entspricht hier einem INNER JOIN, da Datensätze die kein Ergebnis über die Funktion liefern, nicht im Ausgabeset enthalten sind.

Möchte man, wie bei einem OUTER JOIN, alle Datensätze der Abfrage erhalten so muss die OUTER APPLY Variante angewendet werden. Spalten die nicht durch die TVF gefüllt werden können, werden hierbei mit NULL-Werten gefüllt.

Hier jetzt das korrekte Statement. Es zeigt die Umsätze aller Vertriebsmitarbeiter an. Direkte Kundenumsätze werden hierbei durch den CROSS APPLY unterdrückt.

Korrekte Anwendung

 T-SQL | 
 copy code |
?
 
01
WITH Liste AS
02
(
03
    SELECT   SalesPersonID
04
            ,SUM(TotalDue) AS TotalDue
05
    FROM Sales.SalesOrderHeader
06
    GROUP BY  SalesPersonID
07
)
08
SELECT   ci.FirstName
09
        ,ci.LastName
10
        ,ci.JobTitle
11
        ,ci.ContactType
12
        , l.TotalDue
13
FROM Liste l
14
    LEFT join HumanResources.Employee e ON e.EmployeeID = l.SalesPersonID
15
    cross apply dbo.ufnGetContactInformation(e.ContactID) ci
16
 
17
ORDER BY LastName

Man kann ja mal das selbe Statement mit OUTER APPLY probieren

have fun