May 20, 2008 at 11:46 am
Hi guys. this is my first time posting so I hope this is the correct place for this post.
I am having a problem using try/catch with my stored procedures.
Basically I have a Main SP (say SP1) which inserts values into a table variable and then loops through each record in this table and calls another SP (say SP2) to process the row.
My problem is that when some problem occurs with a row in SP2 I want the processing for this row to be rolled back, but i also want the processing for subsequent rows to continue regardless.
Below is just a simplified example of what i'm trying to...
SP1
DECLARE @TempTab TABLE
(
SampleID INT
)
DECLARE @LoopID INT,
@MaxID INT
-- insert dummy entries
INSERT @TempTab(SampleID) SELECT 1
INSERT @TempTab(SampleID) SELECT 2
INSERT @TempTab(SampleID) SELECT 3
SELECT @MaxID = MAX(SampleID) FROM @TempTab
SELECT @LoopID = MIN(SampleID) FROM @TempTab
-- Loop through each row
WHILE @LoopID IS NOT NULL AND @LoopID <= @MaxID
BEGIN
-- call SP2 to process row
EXEC SP2 @LoopID
-- move to next ID
SELECT @LoopID = MIN(SampleID) FROM @TempTab WHERE LoopID > @LoopID
END
SP2 (@SampleID passed in as a parameter)
BEGIN TRY
BEGIN TRANSACTION T1
-- Do some processing on @SampleID
-- Do some other processing on @SampleID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Using the above structure, say some error occurs while processing SampleID = 2, I want the changes for SampleID 2 to be rolled back but for SampleIDs 1 and 3 to continue as normal (i.e. I want the changes made for SampleID 1 to remain and I also want the code to continue on and process sampleID 3 which should be processed AFTER the error occurs in sampleID 2).
Is there some errors in my code above?
The problem with this code when I use it is that when the ROLLBACK is reached for sampleId 2, the code doesn't seem to process any further and SampleId 3 never gets processed.
Any ideas what i'm doing wrong? Thanks.
May 20, 2008 at 11:56 am
Add "declare @Err int" to the first proc, then change "exec sp_2" to "exec @err = sp_2".
Try that, and see if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2009 at 1:12 pm
You can accomplish this behaviour using savepoints. Begin the transaction in the outer batch. In the procedure, do not create a new transaction, create a savepoint using the command "Save Tran name". The Try..Catch block in your procedure will rollback to this savepoint when/if an error occurs. To demonstrate...
-- 1 Here is your original sample modified with a transaction
BEGIN TRANSACTION
DECLARE @TempTab TABLE
(
SampleID INT
)
set nocount on
DECLARE @LoopID INT,
@MaxID INT
-- insert dummy entries
INSERT @TempTab(SampleID) SELECT 1
INSERT @TempTab(SampleID) SELECT 2
INSERT @TempTab(SampleID) SELECT 3
SELECT @MaxID = MAX(SampleID) FROM @TempTab
SELECT @LoopID = MIN(SampleID) FROM @TempTab
-- Loop through each row
WHILE @LoopID IS NOT NULL AND @LoopID <= @MaxID
BEGIN
-- call SP2 to process row
exec dbo.SP2 @LoopID
-- move to next ID
SELECT @LoopID = MIN(SampleID) FROM @TempTab WHERE SampleID > @LoopID
END
COMMIT TRANSACTION
-- 2 Here is the procedure with the savepoints.
Create Procedure dbo.SP2 @ID integer AS
BEGIN
Save Tran t1;
BEGIN TRY
IF (@ID = 2)
Insert Into dbo.TestTable (ID) Values(@ID)
Else
Insert Into dbo.TestTable (ID, Flag) Values(@ID, @ID)
END TRY
BEGIN CATCH
Rollback Tran t1;
END CATCH
END
-- 3 I created a sample table to use to generate an error for ID2.
Create table dbo.TestTable (ID integer NOT NULL PRimary Key, Flag integer NOT NULL)
-- Run your original code after creating the sample table and procedure and you will see that only rowID's 1 and 3 are committed.
December 16, 2009 at 1:29 pm
Additionally it would probably be a good idea to do a check before trying the rollback. It is a good practice to ensure the transaction can be rolled back.
Possible way to do this with savepoint involved:
IF (XACT_STATE()) <> 0 BEGIN --check that there is a transaction
IF (XACT_STATE()) <> -1 BEGIN --check the savepoint can be rolled back
ROLLBACK TRANSACTION SAVE_POINTNAME;
END
ELSE BEGIN --otherwise we need to roll them all back
ROLLBACK TRANSACTION;
END;
END;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply