November 20, 2009 at 1:51 am
I have a formula that I want to place in a scalar-function so it can calculate and return a result.
Problem is ... in this formula there are some INSERT/UPDATE statements that must be executed in order for the formula to be successful.
INSERT/UPDATE are not allowed in scalar-functions.
I tried to create a table-valued function that will return a recordset of data that i can then use to INSERT/UPDATE in the procedure that uses the function. But a table-valued function seems to be just a view with parameters.
Any ideas on how to solve this?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 20, 2009 at 2:17 am
On the face of it, this sounds more like a procedure with an output parameter than a function.
Functions tend to suck badly as soon as they involve data access.
If you could give some more details about what it is you are looking to achieve, perhaps with a simplified example or two...?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 20, 2009 at 2:34 am
Well the formula in short works like this.
------------------------------------------
Get Last Value from a History Summary table
Get Summarized Value from a Current Transaction table
Insert the current value into History Summary so it can be used next month.
Calculate difference between current and history values.
Apply certain rates and values to the difference
Return the calculated difference value.
------------------------------------------
Although the formula is a bit more complex as described above, i really need to find a way to keep it in a function. But if its not possible, I might have to resort to other solutions.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
November 20, 2009 at 2:56 am
That really does seem like it would be best done in a procedure, with the calculated difference value returned in an OUTPUT parameter. Is there any particular reason you are inclined to use a function here? Is there something making the use of a procedure problematic to you?
Alternatively, would it be possible to re-arrange things so that you just pass values to the function, and get a value back? Moving the data manipulation outside the function will allow you to encapsulate the mathematical manipulations inside the function, while keeping data access outside, in a procedure. The procedure should use a transaction to ensure everything stays consistent.
The point is that procedures are optimized for data access (including query plan re-use) and transaction handling.
Functions are intended to be more like their mathematical namesakes than functions in a computer language.
There are extremely good reasons for the distinction.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 20, 2009 at 4:27 am
Thanks Paul.
It seems like I have to go back to ye ol' drawing board and rethink my approach to this problem. :unsure:
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply