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.

CROSS APPLY

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

)

 

Again, easy.

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.

OUTER APPLY

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

Summary

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!