January 31, 2009 at 12:00 am
Hi all
I do have One stored procedure that calls other stored procedures, each on which might call another stored procedure. However, am making begin transaction, commit transaction and rollback only in the main stored procedure, unfortunatlly, i have founf that, if the last exectuted "SP" has encountered a problem the main SP will not rollback as long as it wasn't generated in it, rather than that it commits the transaction! :w00t:
I gently, however, ask u to aid me!!
Thx in advanced
January 31, 2009 at 10:10 am
Probably something's missing in the error handling. Can you post the outer stored proc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2009 at 7:06 pm
ammarghanem921 (1/31/2009)
Hi allI do have One stored procedure that calls other stored procedures, each on which might call another stored procedure. However, am making begin transaction, commit transaction and rollback only in the main stored procedure, unfortunatlly, i have founf that, if the last exectuted "SP" has encountered a problem the main SP will not rollback as long as it wasn't generated in it, rather than that it commits the transaction! :w00t:
I gently, however, ask u to aid me!!
Thx in advanced
Rollbacks are awefully expensive... especially on transactions that are so long and have so many procs involved. The best way to do this would be to know all of the answers before you even think of a insert or update. Calculate everything ahead of time and once everything has been validated as correct, THEN start a transaction that will very quickly do the necessary updates/inserts/delets using, perhaps, a temp table as the driver and source of the validated data. Your current method is RBAR on steriods with the full capability of producing deadlocks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2009 at 11:55 pm
CREATE PROC SP_validatePayment
@AmountPaid numeric(9),
@MethodOfPayment int ,
@ListOfBillsPaid nvarchar(4000)
Begin
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
-------------------------------------
if valid(/*validationProcess*/)
BEGIN
EXEC SP_InsertPayment @AmountPaid ,@MethodOfPayment
END
-------------------------------------
IF( @@ERROR <> 0 )
GOTO Cleanup
IF( @TranStarted = 1 )
BEGIN
COMMIT TRANSACTION
END
return 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN -1
end
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply