On 18 Aug 2004 10:26:18 -0700, Steve D wrote:
I've looked all over but can't find a solid answer. I've got a
function that runs from a View and when the function runs the first
time it is calculating a Temperature for a group of Formulas. What I
want to do is calculate this temperature in the UDF on the first pass
and store it somewhere (table, global variable etc.). On every
execution after that I'd like to use the value stored so I don't have
to recalculate the value again for the specific group defined.
I've looked at storing the data in a table from the UDF but UDF's
don't support dynamic SQL statements and can't run a stored procedure.
The key here is we have a view that calls a UDF (user-defined
function). Using SQL 2000.
Any ideas would be very helpful. Thanks.
Sorry, but you're out of luck. UDFs can't have "side effects." Quote from
SQL Server Books Online:
The statements in a BEGIN...END block cannot have any side effects.
Function side effects are any permanent changes to the state of a resource
that has a scope outside the function such as a modification to a database
table. The only changes that can be made by the statements in the function
are changes to objects local to the function, such as local cursors or
variables. Modifications to database tables, operations on cursors that
are not local to the function, sending e-mail, attempting a catalog
modification, and generating a result set that is returned to the user are
examples of actions that cannot be performed in a function.
However, there's a good chance that the optimizer won't call your UDF
repeatedly. If the values being passed to the function are the same for all
members of a group, chances are the optimizer will notice and only call the
UDF once.