In my never ending quest to completely phase out SQL cursors, I am truly discovering some truly awesome features about SQL Server that I have never looked deeply enough before to consider.

My requirement: I need to determine the total value for a project based on a set of monthly deliverables. These deliverables each contain a start and end date, a monthly revenue value, and a monthly set of hours.

For example:

What makes this interesting is that for those months in which the contract either doesn’t start on the 1^st or end of the last day of the month, the Monthly Revenue must be prorated out: Total Days/Revenue Per Day

I created the following Table Valued function to handle the calculations:

FUNCTION [dbo].[fnCalculateManagedServicesDeliverableTotals]

(

    @deliverable_id int,

    @published int

)

RETURNS @RtnValue table

(    

    deliverable_id int,

    published int,

    total_hours money,

    total_revenue money

)

My goal, as I said, is to calculate the total project value. Before I gave up cursors, I would have simply created a cursor that iterated each project deliverable and updated some variables with the results of my Table Valued function. I wanted to accomplish the same thing WITHOUT USING A CURSOR.

The first thing I did was to create and populate a table variable to hold my project deliverables:

DECLARE @deliverables TABLE

(

    deliverable_id int

)

I then created another table variable to hold my results:

DECLARE @work TABLE

(

    deliverable_id int,

    total_amount money,

    total_hours money

)

This is where it got a little sticky for me. I wanted to insert into my @work table the deliverables from my @deliverables table, along with the results of my Table Valued function.

Initially I tried this and got a SQL syntax error: L

INSERT INTO @work

(

    deliverable_id,

    total_amount,

    total_hours

)    

SELECT

    pd.deliverable_id,

    d.total_revenue,

    d.total_hours

FROM

    @deliverables AS pd

    INNER JOIN dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published) AS d

WHERE

    pd.deliverable_id = d.deliverable_id

It turns out that this is entirely possible, as long as you are not passing in a value or variable to the table function as parameter, but NOT a column from the joining table.

SQL Server does have a solution for this called CROSS APPLY. If you use CROSS APPLY for INNER JOINS and OUTER APPLY for LEFT OUTER JOINS, then you have the ability to create a join between two table valued expressions, which in my case is a TABLE VARIABLE and the results of a TABLE VALUED FUNCTION.

INSERT INTO @work

(

    deliverable_id,

    total_amount,

    total_hours

)    

SELECT

    pd.deliverable_id,

    d.total_revenue,

    d.total_hours

FROM

    @deliverables AS pd

    CROSS APPLY dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published) AS d

WHERE

    pd.deliverable_id = d.deliverable_id

Here are my results: