September 12, 2018 at 3:38 pm
I create a store procedure with Begin and Rollback transaction with Being Try and Catch and GOTO statement but getting an error in my store procedure below.I am trying to capture each statement step where it failed and immediately GOTO rollback transaction with specific step error message. My store procedure syntax is as below. Please advise. Thanks.
Msg 3903, Level 16, State 1, Line 449
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION"
CREATE PROC WYZ
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO AA .....
END TRY
BEGIN CATCH
SET @ErrorStep ='INSERT INTO AA table Erro........r'
GOTO ABORT_TRAN
END CATCH
BEGIN TRY
Update ABC Set =.......
END TRY
BEGIN CATCH
SET @ErrorStep ='Update ABC table error.........'
GOTO ABORT_TRAN
END CATCH
BEGIN TRY
Delete DEF table ........
END TRY
BEGIN CATCH
SET @ErrorStep =' Delete DEF table error........'
GOTO ABORT_TRAN
END CATCH
COMMIT TRANSACTION
END TRY
BEGIN CATCH
GOTO ABORT_TRAN
END CATCH
ABORT_TRAN:
SELECT ERROR_MESSAGE() +'On Error step: ' + @ErrorStep
ROLLBACK TRANSACTION
END
GO
September 12, 2018 at 4:10 pm
Don't use GOTO.
Try reading this and using this sort of structure instead: http://qa.sqlservercentral.com/articles/Data+Mart/67606/
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 12, 2018 at 7:27 pm
You paid so much attention to catching errors that you forgot about error-less run.
What if there are no errors?
You execute
COMMIT TRANSACTION
END TRY
then you skip CATCH ang straight to ABORT_TRAN with its ROLLBACK TRANSACTION.
Since you have already committed the transaction ROLLBACK has no transaction to roll back.
_____________
Code for TallyGenerator
September 12, 2018 at 9:07 pm
CREATE PROC WYZ
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO
September 13, 2018 at 5:18 am
andycadley - Wednesday, September 12, 2018 9:07 PMDon't use goto. Use a single try/catch block for the entire operation instead, i.e.
CREATE PROC WYZ
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTIONSET @ErrorStep ='INSERT INTO AA table Erro........r'
INSERT INTO AA .....SET @ErrorStep ='Update ABC table error.........'
Update ABC Set =.......SET @ErrorStep =' Delete DEF table error........'
Delete DEF table ........COMMIT TRANSACTION
END TRY
BEGIN CATCHSELECT ERROR_MESSAGE() +'On Error step: ' + @ErrorStepROLLBACK TRANSACTION
END CATCH
END
GO
Yeah, but what if you can't figure out which one of your 10k rows caused the error? I like Mel Sansone's approach (link above in my post), try the set of data, if it works great, if not, fail and start row-by-row processing, log the errors when they happen but get the good ones in.
Thom has a good point in the comments about using some sort of exponential search approach for large data, cutting the set in half each time it fails and re-trying, because you might just have one row out of a million causing you issues. I haven't done that myself because I don't work with data that large, but it's a good idea.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 13, 2018 at 11:32 am
jonathan.crawford - Thursday, September 13, 2018 5:18 AMYeah, but what if you can't figure out which one of your 10k rows caused the error? I like Mel Sansone's approach (link above in my post), try the set of data, if it works great, if not, fail and start row-by-row processing, log the errors when they happen but get the good ones in.Thom has a good point in the comments about using some sort of exponential search approach for large data, cutting the set in half each time it fails and re-trying, because you might just have one row out of a million causing you issues. I haven't done that myself because I don't work with data that large, but it's a good idea.
Sure, if you're dealing with thousands of rows of potentially unreliable data you might have need for a more intricate procedure, but there is nothing in the OP that actually suggests that is the case. It might very well just be a series of operations for adding a single item into the database.
September 13, 2018 at 1:17 pm
Thanks Everyone. I made the change base of andycadley recommendation. I did not get any errors on capture specific SQL statement because I do not have any errors to capture for now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply