stored proc recursion in SQL Server

Question

I have a situation where I want to have a stored proc returning a table that calls itself recursively as part of its calculation.

Unfortunately SQL Server is having none of this and gives me an error along the lines of both being unable to declare a cursor that already exists and about not being able to nest and insert exec statement.

Could I get around some of these issues by using a function? Is there another better way to do this?

The calculation is inherently recursive in nature, so there isn't any getting around this using joins as far as I can tell.

EDIT: to clarify the actual calculation since the code is complicated by other stuff and might complicate the matter-

suppose table A has columns (containerID, objID, objType, weight) and table B has columns (itemID, value).

objType in table A tells you whether objID in table A is a containerID (again in table A) or is and itemID from table B.

(containerID, objID) is a primary key on table A as is itemID on table B.

Generally a container will have tens to hundreds of items or other containers in it. Hopefully the recursion depth isn't more than a dozen levels. (guessing) The calculation is to get a weighted average.

Answer 1

I think you get an error because the same cursor name is probably used by every recursive call, and the nested call can't open a cursor of the same name until the parent call closes the cursor. If possible, can you make the cursor name dynamic, maybe something as simple as SOME_CURSOR_{$RECURSION_DEPTH}, and you might have to add the recursion depth as a parameter to the procedure though. I've never done anything like this in SQL Server though so I'm not 100% sure.

Not sure about the next/insert exec problem, though it might be tied to the cursor.

Answer 2

Declaring the cursor with LOCAL scope may resolve the issue. Although I'm not sure how the cursor would act in a recursive context.

Check out this article: http://msdn.microsoft.com/en-us/library/ms189238.aspx

Answer 3

you provide very little information, as a result here is a guess: try using Recursive Queries Using Common Table Expressions, try set based operations and not a cursor, or try using dynamic SQL.

Answer 4

This article gives 7 different ways to do what you're trying to do.

1. Recursive CTE methods
2. The blackbox XML methods
3. Using Common Language Runtime.
4. Scalar UDF with recursion
5. Table valued UDF with a WHILE loop.
6. Dynamic SQL
7. The Cursor approach.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#_Toc205129484