I have a stored procedure with the following structure and I would like to find out how to catch my error from each block, and raise them if transaction is rolled back in the end. As you can see, I am on SQL Server 2000, so no TRY CATCH functionality is supported, please let me know, specificaly I am looking for the code that would catch the raiserror msgs from each block, and return in the block all the way in the end, where it says if @@error condition. Thanks and here is the code:
--and so forth
SET NOCOUNT OFF
BEGINBEGIN TRANSACTION ------------------------------- Block 1 Starts
IF@@ROWCOUNT = 0 BEGIN RAISERROR('Save Application Cosigner not updated', 1, 1) END
------------------------------ Block 1 Ends
-- Similar to block 1 I have block 2,3 and so forth with similar structure, and in the end I -- commit transaction as you can see below.
IF @@Error > 0ROLLBACK TRANSACTION ---- Raise an error with the details of the exception RETURN
Todas las respuestas
u can make use of goto....
if @@error <>0
if @@error <>0
--raise error a custom message
-- u can keep a table with custom error messages and use them to get the exact error message in goto.... or use the config variables in raiseerror and set correct values for them to form an error statement...
You need to check @@ERROR after each block. Because error in block #1 doesn't stop execution of block #2. Also @@ERROR save last error id
For example, following code:
sp_addmessage50001, 16, 'Error1'
sp_addmessage50002, 16, 'Error1'
Msg 50001, Level 16, State 1, Line 2
Msg 50002, Level 16, State 1, Line 5
Also, if severity level less than 11, then @@ERROR==0
So what you are saying is that I can do this in the end and it should work:
IF @@Error > 0
Because I am already doing RAISEERROR if you notice my code block where the sp is called, please clarify/confirm.BEGIN EXEC dbo.uspSaveApplicationReference @CoborRef2 IF @@ROWCOUNT = 0 BEGIN RAISERROR('Save Cosigner Reference 2 not updated', 1, 1) END END
Thanks and please let me know.
yes....as whenever an error occurs, we r passing the control to GOTO... so u need to handle it just there...
have a look at this link ... http://www.codeproject.com/database/sqlservertransactions.asp