On Error Resume Next in SQL - SQL Server Forums

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

Transact-SQL (2000)

On Error Resume Next in SQL

Author

Topic

ratcho
Starting Member

18 Posts

Posted - 2003-12-09 : 11:26:39

Hello to all!

I need something like ON ERROR Resume Next while using nested stored procedures.
I have 2 stored procedures: spA calls spB.

CREATE PROCEDURE spA
AS
....(some code)

exec spB

...(some code)
GO

I want to be sure that spA will continue even if spB produces errors.
How can I do this!
Please help me!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-09 : 11:34:55

>> I want to be sure that spA will continue even if spB produces errors.
You can never guarantee that this will be possible as a lot of errors abort the batch.

Error handling is implemented by checking @@error after each statement and acting accordingly.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-09 : 12:03:09

You would need to ensure spA has proper error handling and returns a code indicating success or failure. Then, from that code, you would determine if spA succeded. As Nigel says, if you don't trap errors in spA, the entire thing may terminate.

- Jeff

ratcho
Starting Member

18 Posts

Posted - 2003-12-09 : 12:43:09

Thanks a lot guys!

As I understood I have to write something like this:
---1st solution---------
CREATE PROCEDURE spA
AS
....(some code)

exec spB
IF @@Error<>0
BEGIN
(don't do anything)
END

...(some code)
GO

-- OR --
--2nd solution---------

CREATE PROCEDURE spA
AS
DECLARE @return_Err
....(some code)

exec @return_Err=spB

...(some code)
GO

--AND---
CREATE PROCEDURE spB
AS
.....(I have only one statement here)
IF (@@Error<>0)
RETURN 1
ELSE
RETURN 0
END
GO

I will try this. Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources