Stored-Procedure (SP) - Executed as single transaction?
-
Hello! I will like to know following in context of SQL Server 2005: 1. Is 1 SP executed as 1 transaction? 2. In case of BEGIN TRAN/TRY/CATCH blocks, what happens if the following scenario occurs: create proc... declare x.... BEGIN TRAN BEGIN TRY .... COMMIT TRAN END TRY BEGIN CATCH .... ROLLBACK TRAN END CATCH .... .... end will the SP be rolled back till start? or just the part after END CATCH? or just the statement causing exception? Tried playing with SP, but seriously confused myself!!! If anyone could provide useful link related to the problem, it will be great! Tried googling but could'nt find anything useful. :( Regards, Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
-
Hello! I will like to know following in context of SQL Server 2005: 1. Is 1 SP executed as 1 transaction? 2. In case of BEGIN TRAN/TRY/CATCH blocks, what happens if the following scenario occurs: create proc... declare x.... BEGIN TRAN BEGIN TRY .... COMMIT TRAN END TRY BEGIN CATCH .... ROLLBACK TRAN END CATCH .... .... end will the SP be rolled back till start? or just the part after END CATCH? or just the statement causing exception? Tried playing with SP, but seriously confused myself!!! If anyone could provide useful link related to the problem, it will be great! Tried googling but could'nt find anything useful. :( Regards, Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
It depends. If your procedure is called from w/in an existing transaction then commit/rollback will affect both the inner and outer transactions. You could use @@TRANCOUNT to determine wither or not you need to rollback or commit.
BEGIN TRY IF @@TRANCOUNT = 1 COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION END CATCHThis way if your stored proc is the outer transaction it will commit on no errors. Then if there is an error in a nested transaction that was rolled back you won't get an error trying to rollback. Search google for: sql server nested transactions You should get plenty of results explaining this topic.Mark's blog: developMENTALmadness.blogspot.com
-
It depends. If your procedure is called from w/in an existing transaction then commit/rollback will affect both the inner and outer transactions. You could use @@TRANCOUNT to determine wither or not you need to rollback or commit.
BEGIN TRY IF @@TRANCOUNT = 1 COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION END CATCHThis way if your stored proc is the outer transaction it will commit on no errors. Then if there is an error in a nested transaction that was rolled back you won't get an error trying to rollback. Search google for: sql server nested transactions You should get plenty of results explaining this topic.Mark's blog: developMENTALmadness.blogspot.com
Just a simple question: Will SP be rolledback if any of its statements fails? Example: create proc xyz insert.... update.... insert.... update.... end exec xyz Will top 3 statements be rolled back if last update fails (no begin/end tran or try/catch involved)? Regards, Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
-
Just a simple question: Will SP be rolledback if any of its statements fails? Example: create proc xyz insert.... update.... insert.... update.... end exec xyz Will top 3 statements be rolled back if last update fails (no begin/end tran or try/catch involved)? Regards, Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
No, outside of a transaction each statement is considered its own transaction. If the last statement fails all the others before it will still be persisted.
Mark's blog: developMENTALmadness.blogspot.com