Errors in stored procedures - 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)

Errors in stored procedures

Author

Topic

rushdib
Yak Posting Veteran

93 Posts

Posted - 2005-04-27 : 13:30:40

The error checking using @@error works only for T-SQL statements, but is there any way to identify the errors which are caused by any other code in a sp? For an example I am using the Bulk Insert command in a sp. I can't capture the error when this fails.

Thanks in advance,

Rushdi

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-27 : 20:34:18

@@Error should capture any errors that are non-fatal.

Read this link.
http://www.sqlteam.com/item.asp?ItemID=2463

Fatal errors abort before you can evaluate the @@Error function.
I would guess you are experience a fatal error (ie, table/file doesn't exist, etc.)

Mike Petanovitch

Kristen
Test

22859 Posts

Posted - 2005-04-28 : 05:08:34

Couldn't the Client Application interrogate the ADO Errors collection to detect such an error?

Kristen

rushdib
Yak Posting Veteran

93 Posts

Posted - 2005-04-28 : 11:25:26

I am using this sp inside a DTS package and this sp is called by a another sp that checks the return value. The problem is my second sp ran eventhough the first one failed.
So what would be the return value (@@error) if there is a fatal error?

Thanks,

Rushdi

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 12:58:43

This could be considered a scope aborting error. What happens is the inner procedure fails and set @@ERROR however the outer procedure continues and the return value of the stored procedure is not set.

Note: Return value of a stored procedure and @@Error are two different things.

I would recommend that you add and out parameter (@error)
and after everystatement in which you want to test you add this

SELECT @error_code = @@ERROR
IF @error_code <> 0 --do something

Example:

--Inner procedure
CREATE PROC usp_inner
       @error_code OUT
AS
BEGIN TRAN
INSERT INTO x_table
VALUES(1,2,3)
SELECT @error_code = @@ERROR
IF @error_code <> 0 GOTO rollback_tran

commit_tran:
COMMIT
rollback_tran:
ROLLBACK

--outer procedure
CREATE usp_outer @error_code OUT
AS
EXEC usp_inner @error_code OUT
IF @error_code <> 0 -- do something

Mike Petanovitch

rushdib
Yak Posting Veteran

93 Posts

Posted - 2005-04-28 : 13:42:54

Thank you for the suggestion.
My code returns @@errorcode from inner proc to outer proc like this
return(@@error). So that @@error is getting reset somehow.

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 14:50:01

I believe the return value isn't being evaluated by the calling proc. Not that @@error is getting reset.

Try it with the output parameter and see if that works.

Mike Petanovitch

This link will help you out:
http://www.sommarskog.se/error-handling-I.html#scope-abortion

check out scope-abortion

also Try capturing the @@ERROR in the outer proc right after calling the inner proc. Example of that in the link above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources