About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).
Showing posts with label rollback. Show all posts
Showing posts with label rollback. Show all posts

Wednesday, May 23, 2007

Rollbacks and Commits in Stored Procedures

So, I ran into this problem of my transaction counts being offset. The problem was that store procedure A has a "Begin Transaction" then calls store procedure B which also has it's transactions of begin, commit, and rollback.

Microsoft says:
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs. This can happen in two ways:

A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a ROLLBACK TRANSACTION statement. @@TRANCOUNT decrements to 0 and causes an error 266 when the stored procedure completes.


A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a COMMIT TRANSACTION statement. @@TRANCOUNT decrements by 1 and causes an error 266 when the stored procedure completes. However, if BEGIN TRANSACTION is executed after the COMMIT TRANSACTION, the error does not occur.
-- http://msdn2.microsoft.com/en-us/library/ms187844.aspx

DECLARE @LocalTransActive Bit
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
Trans_Discharge
SET @LocalTransActive = 1
END

For the commit part:
IF @LocalTransActive = 1
BEGIN
COMMIT TRANSACTION
Trans_Discharge
END

For the rollback part:
IF @@TRANCOUNT > 0
BEGIN
IF @LocalTransActive = 1
BEGIN
ROLLBACK TRANSACTION
Trans_Discharge
END
END