Error Handling / Return Values

Question

Error Handling / Return Values

AndyD

Old Hand

Points: 362
More actions
May 27, 2003 at 8:17 am

#159974

Folks
Suggestions please
In my stored Proc I have the following Code:-
DECLARE @ErrorsExist Int
Select @ErrorsExist = @@Error
If @ErrorsExist = 0
Begin
EXECUTE @ErrorsExist = sp_xml_preparedocument @iDoc OUTPUT, @XmlString
End
Return @ErrorsExist
Why is @ErrorsExist always zero even when Query Analyser returns the following message:-
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 526
XML parsing error: The following tags were not closed: AMCData, cd_lpAMCData.
I have even tried placing the following command direcly after my Execute Statement but it still returns Zero.
Select @ErrorsExist = @@Error
Thanks
Andy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

quote:
DECLARE @ErrorsExist Int
Select @ErrorsExist = @@Error

@@ERROR Returns the error number for the last Transact-SQL statement executed. In your procedure, you assign @@error without executing any SQL statement.

Edited by - Allen_Cui on 05/27/2003 08:31:32 AM

AndyD Old Hand Points: 362 More actions · Answer 2

Thanks for that point Allan but how come my Execute Statement Fails to Set @ErrorsExist When errors do exist ?

Thanks

Andy

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Example from BOL.

C. Use @@ERROR to check the success of several statements

This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @@ERROR after both statements and are used in a shared error-handling routine for the operation.

USE pubs

GO

DECLARE @del_error int, @ins_error int

-- Start a transaction.

BEGIN TRAN

-- Execute the DELETE statement.

DELETE authors

WHERE au_id = '409-56-7088'

-- Set a variable to the error value for

-- the DELETE statement.

SELECT @del_error = @@ERROR

-- Execute the INSERT statement.

INSERT authors

VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',

'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)

-- Set a variable to the error value for

-- the INSERT statement.

SELECT @ins_error = @@ERROR

-- Test the error values.

IF @del_error = 0 AND @ins_error = 0

BEGIN

-- Success. Commit the transaction.

PRINT "The author information has been replaced"

COMMIT TRAN

END

ELSE

BEGIN

-- An error occurred. Indicate which operation(s) failed

-- and roll back the transaction.

IF @del_error <> 0

PRINT "An error occurred during execution of the DELETE

statement."

IF @ins_error <> 0

PRINT "An error occurred during execution of the INSERT

statement."

ROLLBACK TRAN

END

GO