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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-07 : 09:27:30
|
| Keith writes "I have written a stored procedure that uses Transactions. What is the behavior when an unexpected error occurs. It is my understanding that unless I tell the transaction explicitly to rollback it will commit. We had an error occur in this proc and the data seems to have been committed. Is there anyway to check for unexpected errors in a SP with a transaction? Does the SP kick out at the first unexpected bug? I thought I could use @@ERROR? Let me know and thank you KRN" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-07 : 09:38:25
|
| Explicit transactions will not commit unless explicitly committed by default. But an error will not necessarily cause an abvort - it may carry on to the following statements and commit statement unless detected and handled.usually write likebegin tranupdate ...if @@error <> 0beginraiserror...rollback tranreturnendupdate ...if @@error <> 0beginraiserror...rollback tranreturnendcommit tranreturnPlace an error check after every statement - every sql statement will clear the @@error status.Also look at xact_abort==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Transactions within Stored Procedures