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 |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-07-20 : 14:58:23
|
| Hi,I recently started learning about transactions and error handing in stored procedures. I created a stored procedure (which is shown on the bottom) and tried to create a case where it would fail to see that a rollback really happens, but it does not seem to work. What am I missing? Will rollback happen in specific cases only?I tried two different scenarios:1. For the 2nd statement, insert into non-existant table [i.e. insert into ClientPreferences2]2. Start running a stored procedure and stop it in the middle of executionIn both cases, no rollback seems to have happened. In case 1, data got deleted by 1st query and then sp failed with message that table does not exist. Why no rollback? In case 2 sp always seems to finish with all the changes made and committed:CREATE PROCEDURE TEST(@ClientName VARCHAR(255), @PublicationIDList VARCHAR(8000))ASBEGIN SET NOCOUNT ON DECLARE @er INT BEGIN TRANSACTION DELETE FROM ClientPreferences WHERE ClientName = @ClientName SELECT @er = @@ERROR IF @er <> 0 GOTO ERROR_EXIT INSERT INTO ClientPreferences(ClientName, ID, IDType, Include) SELECT @ClientName, ID, 'Publication', 1 FROM fnsplitter(@PublicationIDList) SELECT @er = @@ERROR IF @er <> 0 GOTO ERROR_EXIT COMMIT TRANSACTION RETURN 1 ERROR_EXIT: ROLLBACK TRANSACTION RETURN 0ENDGOPlease adviseThank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-20 : 15:34:06
|
| Tara - thanks for the info. I actually don't expect such a condition to happen - I was just trying to test the stored procedure and come up with a condition which would cause a roll back. Could you tell me which that would be?Thank you! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-20 : 17:35:09
|
| Worked - thanks Tara! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Transactions and error handling