Nested Transactions in Stored Proc

Last Post 09 Mar 2006 05:24 AM by erubin. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
erubin
New Member
New Member

--
08 Mar 2006 06:48 AM
I have some nested stored procedures where one sp calls another, etc. I need this wrapped in a transaction so that if an error occurs on any one sp (either the calling sp or the one that is called) it will fail.

I'm continuall getting this error: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3." So I've been playing around with where to put the Begin Tran, Committ,
Rollback, etc. I've been grappling with this for longer than I care to admit. Please help!!!

Here's some pseudo code:


CREATE PROCEDURE [dbo].[spFILE_PROCESS]

AS

--Perform some queries, etc, then:


Exec spInsert_Customer

Exec spInsert_Trans

---------------------------------------------------


Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer and spInsert_Trans to be their own transaction as I call these sps by themselves
elsewhere in my application.
mwesch
New Member
New Member

--
09 Mar 2006 02:57 AM
The simple answer is that you need a BEIGIN TRANSACTION and a COMMIT/ROLLBACK in each of your 3 stored procedures. That way, if you call the insert procedures individually, they will be covered by the transaction block inside those procedures. Or if you call the file process procedure, it will be covered by the transaction block in that procedure.

The more difficult thing to worry about is how to handle transactional errors in the nested procedures and pass them out it the outer procedure. Typically, you would capture errors into a variable and then pass it back through a return value or output parameter.

One other suggestion. You should avoid naiming your stored procedures with an "sp" prefix. When the T-SQL code runs, it assumes that those are system stored procedures and looks for them in the master database. It only looks in the user database when it doesn't find the stored procedure in the master database. So, it results in extra calls to the database.
erubin
New Member
New Member

--
09 Mar 2006 05:24 AM
Thanks for you help Michael. I think I have it sorted out.

Regarding the "sp" prefix. It is actually "sp_" that is used as the system sp
prefix.
You are not authorized to post a reply.

Acceptable Use Policy