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 |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2005-10-05 : 17:19:08
|
| My stored procedure is a very lenghy one, here is a summary of how it works:CREATE PROCEDURE XXXXXXXXXXX...ASDECLARE @blah INT....BEGIN TRANIF(@blah = 1)BEGIN .. .. ..ENDELSEBEGIN SELECT .. UPDATE ... EXEC SOMESTOREDPROCEDURE @blah <-- ******there was an error in this sprocENDCOMMIT TRAN---------------The *** is where the sproc reported the error, I had misspelled a table and therefore from what I ASSUME is that the entire sproc should have rolled back...but it didn't. An insert had taken place in one of the tables.why did the transaction not rollback? the commit tran was reached but only after an error had occured? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-05 : 17:22:31
|
| After each and every single DML operation (INSERT, UPDATE, DELETE), you need to check the value of @@ERROR to determine whether or not to commit/rollback/continue on down the stored procedure. It doesn't appear that you have done this.Tara |
 |
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-10-05 : 17:36:58
|
| no i haven't, ok I wish there was a way to just have it rollback the entire sproc! oh well... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-06 : 04:08:27
|
| It can be quite hard to "catch" an error in a called sub-procedure.You might want to putSET XACT_ABORT ONat the top of your SProcs to force them to abort (won't solve the underlying "catch" problem, but should cause an SProc to abort if you miss checking an @@ERROR somewhere!)Kristen |
|
|
|
|
|
|
transaction didn't seem to rollback?