Error Handling - What am I doing wrong?

[tadd]

here's my code, which is designed to generate an error:

-- =================================================
DECLARE @ErrNum INT

DECLARE @Msg INT
SET @Msg = 'TEXT'

SET @ErrNum = @@ERROR
IF @ErrNum <> 0
PRINT 'An error occurred and is being handled gracefully.'
ELSE
PRINT 'No errors occured.'
-- =================================================

Execution never reaches the line which gets the value from @@ERROR. WHY??? What's the point in having @@ERROR if you can never read it?

 

[mrdenny]

The error level is level 16 which stops the T/SQL script in it's tracks so you can not continue to the next line.

If you use SQL 2005 you can use the TRY / CATCH blocks to catch the error. In SQL 2000 you don't have the new feature.

When doing error catching in SQL 2000 and below you can only deal with errors that are not considered fatal errors such as a failed insert, or a failed create table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tadd]

Thanks Denny. So what is the best way to a handle fatal error? I don't suppose there is any way to pass back useful information when a fatal error occurs is there? (My stored proc is being called by a webservice)