Thanks for the reference it'll be useful in the future.

Although I don't think Scalar vs ITVF is really a big issue for me as I'm not querying the scalar functions in line with tables.

It's more along the lines of;

....

DECLARE @fSomeSensiblyNameVariable1 AS FLOAT

DECLARE @fSomeSensiblyNameVariable2 AS FLOAT

DECLARE @fSomeSensiblyNameVariable3 AS FLOAT

DECLARE @fSomeSensiblyNameVariable4 AS FLOAT

DECLARE @fSomeSensiblyNameVariableTotal AS FLOAT

SELECT @fSomeSensiblyNameVariable1 = dbo.funSalesVolume(@dTransactionDate, @cProduct, 'Dave')

SELECT @fSomeSensiblyNameVariable2 = dbo.funSalesVolume(@dTransactionDate, @cProduct, 'Steve')

SELECT @fSomeSensiblyNameVariable3 = dbo.funSalesVolume(@dTransactionDate, @cProduct, 'Nigel')

SELECT @fSomeSensiblyNameVariable4 = dbo.funSalesVolume(@dTransactionDate, @cProduct, 'Sammie')

SELECT @fSomeSensiblyNameVariableTotal = @fSomeSensiblyNameVariable1 + @fSomeSensiblyNameVariable2 + @fSomeSensiblyNameVariable3 + @fSomeSensiblyNameVariable4

INSERT INTO tblResults

SELECT @fSomeSensiblyNameVariable1 , @fSomeSensiblyNameVariable2 , @fSomeSensiblyNameVariable3 , @fSomeSensiblyNameVariable4 , @fSomeSensiblyNameVariableTotal

I know this looks convoluted but when 1 report is a PDF 50 pages long with an average of 50 values per page you're looking at 2000 or so distinct metrics (provided by some 30 scalar functions) so this logical code structure allows for uncomplicated maintenance. Although I admit 14000 lines is a lot for any sproc! The metrics that needs to go in this report seem to change quarterly so the more clearly segmented and structured the code the better.

The PDF is then generated by sticking headings down the left of the page and then looping through the tblResults table.

As with anything like this it's down to developer preference and I'm sure if I presented my requirements to 10 of my peers I would get 10 different solutions but this appeals to my ploddy methodical brain.

on a database with several million rows in each table queried this report usually completes (all 2000 function calls x 13 months of data per report) in about 6 minutes so I know it can work efficiently.

The next step, now that I have all the execution plans stored from my profiler trace, is to find out how to make the stored procedure use the right plan!