Way to trap any error - 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)

Way to trap any error

Author

Topic

vooose
Starting Member

10 Posts

Posted - 2004-10-06 : 22:21:01

@@ERROR is not 'passed up' to the calling stored procedure, so that if any error occurs and we inspect

@@ERROR we don't get a value <> 0

I'm wondering if there is a magical setting somewhere that automatically rollbacks everything done since the top level stored procedure was called?

Wal

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-10-06 : 23:55:14

You have to use RETURN values. If the proc is successful RETURN 0, else RETURN 1. In the master procedure, you can:


CREATE PROCEDURE test_return AS

RETURN 0
GO

DECLARE @result INT

EXEC @result = test_return

SELECT @result

You can then use the @result to do error handling in the top levels. This will still not trap any errors above level 16. These actually blow out of the transaction. The only way to handle these is with an application. SQL Server 2005 is changing that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources