Error handling in a complex stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error handling in a complex stored procedure

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-17 : 11:12:19

I've got a complex stored procedure that performs 6 pass-through queries to an Oracle database, uses temp tables, CSV parsing and a few other things. The SPROC runs for 2-5 minutes and is fired by a job. Last night, someone on the Oracle side changed a security setting that caused one of the pass-throughs to fail (table not found) - a critical error.

The user interface is the web where the status table is populated at the end of the stored procedure. When the SPROC fails, the job fails and the status is "processing" and never finishes.

My question is - what is the best way to implement some sort of graceful way to handle errors. I'd like for the code to skip the pull that is bad and go to the next one so that the whole job doesn't fail. This job is pretty critical to us and I don't want it just failing and leaving people stranded.

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-01-17 : 12:40:14

You could try something like this below, i put it in another post as is but it's easily modified.

CREATE PROCEDURE usp_Template
AS
DECLARE
@intErrorCode int,
@TransactionCountOnEntry int
--Error handling code
SELECT @intErrorCode = @@Error
If @intErrorCode = 0
Begin
SELECT @TransactionCountOnEntry = @@TranCount
BEGIN TRANSACTION
End
If @intErrorCode = 0
Begin
<your code goes here>
End

--Error handling code
If @@TranCount > @TransactionCountOnEntry
Begin
If @@Error = 0
COMMIT TRANSACTION --if no errors were encountered then COMMIT
Else
ROLLBACK TRANSACTION --if errors encountered then ROLLBACK
End

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-17 : 14:31:39

OK - Let me work this one in and see how it goes.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources