Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2014-05-30 : 06:43:39
|
| Hi all,If I put a Begin Transaction and End Transaction around a stored procedure like:Begin Transactionexec myprocCommit Transactiondoes that mean everything in the stored procedure will be rolled back if the procedure fails?The reason I ask is because I have a procedure that does a number of inserts into tables, this procedure is scheduled as part of a job.I want to re-run the job step if it fails but I want to make sure that everything has rolled back so that duplicate data is not inserted.Thanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-05-30 : 10:35:23
|
quote:
Originally posted by ranvir_2k
Hi all,If I put a Begin Transaction and End Transaction around a stored procedure like:Begin Transactionexec myprocCommit Transactiondoes that mean everything in the stored procedure will be rolled back if the procedure fails?The reason I ask is because I have a procedure that does a number of inserts into tables, this procedure is scheduled as part of a job.I want to re-run the job step if it fails but I want to make sure that everything has rolled back so that duplicate data is not inserted.Thanks
If there is an error in the stored procedure and the stack unwinds, it will rollback the transaction when the connection terminates (if you are calling it from an application). But if the stored procedure handled the error (via try catch block, for example, or if SET XACT_ABORT is not ON and it was not a batch-terminating error), the call will return and the commit transaction will be executed.When I read back what I just wrote, I realize it can sound confusing. Take a look at this page to get more info: http://msdn.microsoft.com/en-us/library/ms175523(v=sql.105).aspx |
 |
|
|
|
|
|
Rolling back stored procedure