How to use a recursive function to update a table?

Question

This is a follow-up from this question.

Functions are not allowed to write to the database, but what if I wanted to update a record every time a function was called, specifically a recursive function?

Currently, I have a function that takes an ID and returns a float. I'd like to update a table using the given ID and the returned float. Ordinarily, a simple stored procedure could be used to call the function and then make the update. My function is recursive, and so the solution isn't that simple...

I'm thinking about attempting to do this:

• Create the recursive function so that it takes a table as a parameter
• If the table is null, create it; else, copy the table to a new variable (because it will be readonly)
• update the copied table in the function before making the recursive call, and pass the copy to the function
• at the end, return the complete table (not sure how I will "know" is it complete yet)
• call this function from a stored procedure that uses the returned table to make several updates

I'm looking for alternatives before I even try something like that. Seems that this has been done before.

Answer 1

Any recursive implementation is T-SQL will sooner or later run into the @@NESTLEVEL cap:

You can nest stored procedures and managed code references up to 32 levels. The nesting level increases by one when the called stored procedure or managed code reference begins execution and decreases by one when the called stored procedure or managed code reference completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail.

But we know from CS101 than any recursive algorithm can be implemented as an iterative algorithm with the help of a stack. So you need a table to act as a stack (see Using Tables as Queues for some related discussion). Use a stored procedure, not a function, since in T-SQL 'functions' are something special (a data access path basically) and are not allowed to modify data. Whenever you think 'function' (as in C/C++/C# function or method), you really need a stored procedure. You cannot return 'tables' from procedures, so the output has to be a table into which you write the results.

All this is just theory, because you did not provide the actual problem, just a description of the type of problem. If we'd know the real problem, we might say whether a stored procedure makes sense, whether using cursors is OK, whether using a #temp table is the way to go etc etc. And all these just to consider the plain vanilla algorithm. Things can get really hairy if you add size-of-data considerations and concurrency issues.