July 20, 2010 at 1:54 pm
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
If I understand correctly both of the cases I tried are fatal cases. Can someone suggest which test case will show me whether the code is working properly or not?
Thank you!
August 2, 2010 at 9:15 am
@@Error doesn't catch unsexistent objects or missing permissions, all those kind of things tipically happening outside any query. It only catches error inside the queries, as let say, datatype overflow or casting errors. You must take care of unexisting objects explicitly.
For your secont test, cancel doesn't immediately cancel the job. The server waits until is in a position to finish cleanly and then breake the execution, then by the time it reaches that point the commit was already made. Take this into account when processing massive quantities of information ...you cannot easily terminate a 20 million record update in the middle of the process.
By the way, tipically the return 0 is used for "no errors" and anything else as "something happened". You're currently used inverted. I'ts just a convention (you're not forced to use it), but you'll see that all the system procedures uses that same way.
August 2, 2010 at 10:24 am
I typically do this
BEGIN TRAN
DELETE xxx
if @@Error <> 0
begin
rollback transaction
return 1
end
UPDATE xxx
if @@error <> 0
begin
rollback transaction
return 2
end
commit transaction
I want to rollback right away, not go into any type of GOTO statement. And as mentioned above, not everything is cancelled.
Return 0 for success as well, not 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply