June 4, 2008 at 10:24 am
Is there such a thing? I have the following interspersed in my code with the exit procedure at the bottom and it isnt catching when I call the procedure. It is however working (kinda) in my debug code... Please help!!!!
select @ErrCode = @@ERROR, @TranNumber = @@TRANCOUNT
IF @ErrCode <> 0 GOTO error_handler
Set @ErrMsg = 'Error in the Insert to Unit section'
------------------------------
exit_procedure:
IF @TranNumber > 0 AND @LocalTrans = 1 COMMIT TRANSACTION
print 'Successful, record committed' (debug for me)
--RETURN( 0 )
error_handler:
IF @TranNumber > 0 AND @LocalTrans = 1 ROLLBACK TRANSACTION
print 'inside error_handler' (debug for me)
RAISERROR(@ErrMsg, 16, -1 )
--RETURN( -1 )
June 4, 2008 at 10:43 am
At the very least is your ordering. You are never setting @ErrMsg because it is after your goto.
If you are doing multiple inserts\updates in a single transaction why use the the GOTO at all? You could catch your error after each statement and then rollback, raiserror, and return within the IF. Then Commmit at the end.
If the inserts\updates do not need to be in a single transaction, wrap each one in a Begin-Commit-Rollback and "fall-through" to the next one on error logging the error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2008 at 4:35 pm
Fair enough, and thank you for the reply. Now.....
I moved the error code under each statement.
select @ErrCode = @@ERROR, @TranNumber = @@TRANCOUNT
print 'error inside the update unit section'
print convert(varchar(2), @ErrCode)
IF @ErrCode <> 0 and @TranNumber > 0 AND @LocalTrans = 1 ROLLBACK TRANSACTION
when I run my code, I get the error msg printing and the error code = 0 it is pulling the data correctly and when I run it in debug it looks good. When I run it on my local box, the data gets inserted or updated correctly. I don't understand why it would do what it is suppose to but throw the error....any ideas??
June 4, 2008 at 4:52 pm
If you are not getting an error then it should not through the error message. It is hard to tell based on the limited code snippets you have provided. Another thing I noticed in your original post, if you are still using the GOTO, the Return in exit_procedure is commented out so you are falling through to the error handling routine every time.
Check out the links in my signature for how to post questions that will get faster and more accurate answers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply