January 21, 2004 at 9:13 am
I understand the basics about transactions and how to use them I just never have (used them) in a stored procedure.
I have a question about the behavior of a transaction under a particular scenario.
I am writing a stored procedure that will execute numerous other stored procedures. In each sub-procedure I have INSERT statements. If an INSERT statement (or really any statement) fails in a sub-procedure the procedure will end and return an error. I am going to use the SELECT @@ERROR command in the calling procedure to capture the error and act accordingly.
My question is, if I begin a transaction in a sub-procedure and a query statement throws an error and the procedure is exited before the transaction is committed, does the transaction automatically roll back?
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
January 22, 2004 at 6:00 am
The transaction in your sub-procedure will rollback but you will need to tell the calling procedure to rollback if it receives an error message otherwise it will commit everything else that has been performed in the other sub-procedures. Hope this helps.
January 22, 2004 at 6:26 am
It might be helpful to read nested transactions in BOL
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 23, 2004 at 12:18 pm
Use SET XACT_ABORT ON in your calling sproc.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
Hope this helps !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply