Functions with global variables

[php newbie]

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?

[David Portas]

There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--