Storing variables from a user-defined function

[Steve D]

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.

[Ross Presser]

Re: Storing variables from a user-defined function

On 18 Aug 2004 10:26:18 -0700, Steve D wrote:
[color=blue]
> 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.[/color]

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.

[Erland Sommarskog]

Re: Storing variables from a user-defined function

Steve D (steve.debettgn ies@ecolab.com) writes:[color=blue]
> 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.[/color]

As Ross said, you can't do it. You would have to write a stored procedure
instead. This procedure would have to store the data in a table, since
there are no global variables in SQL Server. And, no, you cannot call
stored procedures from views.

It looks like you will have to reconsider your design.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp