How to rewrite SQL cursors in an SQL function?

I have to support an existing sql function that calculates student activities based on items in several tables. This function is called constantly! It is called for calendar displays, outputing schedules and when students participate in an activity they are checked in to the activity determined by the function. (I'm using VB.Net, ASP.net also.) AND of course the function has 4 cursors written throughout so performance is pretty bad, I did an sql profiler. I commented out the function parts for testing purposes and rewrote using # temp tables and while loops. Now trying to install as a function and test, I get the dreaded error message "Cannot access temporary tables from within a fun开发者_如何学Pythonction." Oh and theres more good news: this function is 18 printed pages, it is huge. (Not my definition of a function.) I really cant rewrite the whole thing now so what are my options?

Without knowing any details of what problem your function is trying to solve and the schema underlying the problem, it's hard to give an answer. As a general rule, assuming the underlying model is relatively sound, the use of cursors, while-loops and conditional logic (if-then-else) in SQL is a code smell indicating that the author failed to frame the problem in a set-based manner; rather she approached it procedurally.

Usually, you see this when somebody comes to SQL from a background of imperative programming (C/C++, Cobol, VB, etc.). They are used to dealing with collections (sets) 1 item at a time.

Somewhere in that function is almost certainly a [probably complex] select statement with joins, virtual tables and nested subselects trying to get out, but without knowing the actual details, it's impossible to give any sort of definitive guidance.

FWIW, about the only time I've ever found it necessary to use while-loops or cursors is if I need to manually generate key values or execute a stored procedure individually against each row in a result set. And that is usually because of some other failing in the design or implementation of the system.

The key to SQL is set theory and relational algebra. SQL is at heart a descriptive language with an elegant grounding in mathematics that lets you describe what you want in terms of set operations. Trying to shoehorn procedural semantics onto that is (as you've noticed) a path to poor performance and needless complexity.

Two approaches here:

1. Try to restate the problem as simple SQL using a four table join. You will probably need some "CASE" statements to implement some of the logic in the "Function".
2. Examine the function and look for data elements that can be computed and stored at update time. I.E. move some the burden of calculation to UPDATE/INSERT process and store the results to make it easier for the querying processes.