How to handle Errors in a Stored Procedure in SQL Server 6.5

[PeaceMaker]

I have written a stored procedure which consists of 3000 or more lines....

Now it is in transaction... i want to rollback if any of my sql statement fails during the processing..

I want to commit only when my Stored procedure is complete

I am using @@Error variable to find out whether the statement was a success or not...

My problem is that I have to check this @@Error variable after every DML statement...

Is there any way that i can write something like
On Error Goto ErrorHandler

And in that errorHandler i would like to rollback the transaction and terminnate the stored procedures


Please help as my 3000 lines of SP has about 200 lines of DML statements...

and the way i am handling erros
if @@Error <> 0
begin
Rollback transaction
Return
end
it means 200*5=1000 extra lines of code just for this purpose...

Please suggest...
Thanx

Ajay Handa (PeaceMaker)

 

[TomSark]

Hi,

You can't trap for errors with something as easy as in a VB error handler in a proc. You have to use a GOTO statement to do something remotely similar to this, but you still have to check for errors after each DML statement in a proc as in:

BEGIN TRANSACTION

DML statement

IF @@error <> 0
GOTO ErrHandler

More Code...

Bottom of proc...

COMMIT
RETURN 0

Errhandler:
RAISERROR ('Some Error Message',10,1)
ROLLBACK TRANSACTION
RETURN 1

This will reduce, but not eliminate the number of lines of code required in your proc.

If you are using ADO or MTS or whatever, I would suggest you do your error checking and transaction management there instead (unless you are not calling the proc from a front-end app(s), but calling it primarily from the back end). This way, you can leverage the power of error checking in the front-end (as in VB, VC++ or whatever).

Tom