Transactions in a recursive sproc?
-
Hi all,
I hope someone can give me some advice on this; basically, I'm putting together a recursive stored procedure which will copy a level in a hierarchical structure, including any sub-levels it might be the parent of. OK, the logic of the above is pretty straightforward, but something I'd like to do is wrap the whole call in a transaction
Obviously, I can't begin the transaction in the sproc itself, or a new transaction would be created every time the sproc is recursed.. Is that true? Or is it clever enough to know that it should be using the first transaction started?
Hope someone can help me out here.. Thanks in advance!
Andy
-
Well you could have a parameter name @NestedLevel as tinyint that defaults to 0, then on every recall of the proc, you increment that value. Then in the code if have
: if @NestedLevel = 0
begin tran
then after the recursive call is made and al other task of the proc complete :
: if @NestedLevel = 0
commit tran
-
Now that is a grand plan - thank you kindly!
-
Let me know if you need more help.
-
It might be worth noting that recursion is limited to 32 for executing recursive functions in SQL Server. This isn't a lot if your dealing with a complex
hierarchy.
-
Wow, thanks for the heads up! Definitely something to bear in mind... I think we should be OK with that limit, but it's certainly a bit of a drawback
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply