Let’s finish up with the SQL 210 series today with the APPLY operator. After you learn how to create your own functions, you’re going to want to use them in new and interesting ways. Learning to use APPLY is like learning to use joins for functions. It let’s you run the function against multiple rows at a time.
Just like joins, you’ve got a couple different APPLY functions.
The CROSS APPLY is the INNER JOIN for functions. It only returns rows from your source table where you have a result in your function. Let’s take an easy query that shows you sales by sales person.
SELECT c.FirstName, c.MiddleName, c.LastName, SUM(TotalDue) AS TotalSales FROM sales.SalesPerson sp INNER JOIN HumanResources.Employee e on sp.SalesPersonID = e.EmployeeID INNER JOIN Person.Contact c on e.ContactID = c.ContactID INNER JOIN Sales.SalesOrderHeader soh on sp.SalesPersonID = soh.SalesPersonID GROUP BY c.FirstName, c.MiddleName, c.LastName
Now, let’s turn that SalesOrderHeader lookup into a function.
CREATE FUNCTION f_getTotalSales(@SalesPersonID INT) RETURNS TABLE AS RETURN ( SELECT SUM(TotalDue) as TotalSales FROM Sales.SalesOrderHeader soh WHERE SalesPersonID = @SalesPersonID )
Now, let’s replace the join to SalesOrderHeader into a function call.
SELECT c.FirstName, c.MiddleName, c.LastName, f.TotalSales FROM sales.SalesPerson sp INNER JOIN HumanResources.Employee e on sp.SalesPersonID = e.EmployeeID INNER JOIN Person.Contact c on e.ContactID = c.ContactID CROSS APPLY f_getTotalSales(sp.SalesPersonID) f
You simply replace the INNER JOIN to SalesOrderHeader into the CROSS APPLY. The difference in syntax comes in when you pass in the value of the SalesPersonID you’re trying to find the sales total for, rather than joining ON a criteria.
The OUTER APPLY is the OUTER JOIN for functions. It will show you all the rows from your source table, even if they don’t have a result in your function. In our case we’re going to need to create a new sales person. Let’s add someone with a sales title, who isn’t already a sales person.
INSERT INTO sales.SalesPerson (SalesPersonID) SELECT EmployeeID FROM HumanResources.Employee e LEFT JOIN sales.SalesPerson sp on sp.SalesPersonID = e.EmployeeID WHERE title like '%sales%' and sp.SalesPersonID IS NULL
Now we know we have sales people without and sales yet. So when we run our CROSS APPLY now, I see that Brian Welcker needs to get busy making some sales, he’s got a lot of ground to cover to catch up with the rest of the pack.
SELECT c.FirstName, c.MiddleName, c.LastName, f.TotalSales FROM sales.SalesPerson sp INNER JOIN HumanResources.Employee e on sp.SalesPersonID = e.EmployeeID INNER JOIN Person.Contact c on e.ContactID = c.ContactID OUTER APPLY f_getTotalSales(sp.SalesPersonID) f
Learning to apply functions to queries is a very useful skill to have, especially when you want to start playing with dynamic management views. A couple of the really cool views are actually functions, and if you want to get useful data out, you’re gonna have to use the APPLY operator, It’s not that hard to learn them, just draw the analogy to joins in your mind, and it’ll click.
Well, that’s it for my SQL 201 series. For the next few months, I’m going to be studying more for the MCM, and playing with SQL 2012. As always I’ll be sharing my findings here. If you have any questions on learning more SQL, just send them in. I’m here to help!