Error Handling in SQL Server 2000 - Need Help

Question

 

Hi Everyone:

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:

CREATE PROCEDURE [dbo].[uspSaveLoanApplicationMain]

@Bor_BorrowerGuid uniqueidentifier,

@Bor_FirstName varchar(15),

@Bor_LastName varchar(35),

--and so forth

AS

SET NOCOUNT OFF

BEGIN

             BEGIN TRANSACTION

              ------------------------------- Block 1 Starts

              EXEC sp--

              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.

             COMMIT TRANSACTION

             RETURN

             IF @@Error > 0

             ROLLBACK TRANSACTION

             ---- Raise an error with the details of the exception

             RETURN

END

 

Thanks.

 

 

Answer 1

u can make use of goto....

Create proc

@a,@b

AS

block 1

----statements

if @@error <>0

goto ErrorBlock

block 1

----statements

if @@error <>0

goto ErrorBlock

return 0

ErrrorBlock :

rollback transaction

--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...

 

Answer 2

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_addmessage 50001, 16, 'Error1'

sp_addmessage 50002, 16, 'Error1'

begin

RAISERROR (50001,16,1)

end

begin

RAISERROR (50002,16,1)

end

print @@ERROR

return

Msg 50001, Level 16, State 1, Line 2

Error1

Msg 50002, Level 16, State 1, Line 5

Error1

50002

Also, if severity level less than 11, then @@ERROR==0

Answer 3

So what you are saying is that I can do this in the end and it should work:

 IF @@Error > 0

ROLLBACK TRANSACTION

PRINT @@Error

RETURN

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.

Answer 4

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