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 execution
In 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)) AS BEGIN
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 0 END GO
Please advise
Thank 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 |
|
|
|
|