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!
^ Thats me!
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010