MSSQL: Catch SQL Error in a stored procedure

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

SQL Server Development (2000)

MSSQL: Catch SQL Error in a stored procedure

Author

Topic

Nicoh56
Starting Member

17 Posts

Posted - 2004-10-19 : 10:31:29

Hi ,

I need to catch the error message MSSQL Server 2000 gives when any transaction fails.

I mean, I have a SQL job running. The only step is a stored procedure which launch multiple stored procedure.
If one stored procedure fails, I would like to catch the SQL Error in the main stored procedure in order to store this error into a table in my database

Is it possible?
I know there is @@Error, is @@error a local variable or a global one?
For example, if there is another job or another query running during the same time as my job, and if this other job/query raise an error, does @@error will have something in?
Moreover, what is the default value for @@error?

Thanks for your help.

Regards

Nicolas

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-19 : 10:48:57

@@error returns the error number for the last Transact-SQL statement executed.


declare @err int

Begin Transaction
exec blah blah blah.....
select @err = @@error
   If @err <> 0 
     Begin
      Your error handling ...
       Rollback Transaction
       Return @err
     End

You must trap @@error imediately after what you want to trap of it will reset.

-Jon
Now a "Yak Posting Veteran".

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 14:15:42

Might help if you put

SET XACT_ABORT ON

at the top of the Child SProc

Kristen

Nicoh56
Starting Member

17 Posts

Posted - 2004-10-20 : 03:36:14

Hi,

Thanks for your answers!
How about the trapping of the error message (@@error trap only the error code)?

I have seen in that post (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26324) that there is a solution:


DECLARE @Err INT

-- Your SQL Here
SET @Err = @@Error
If @Err<>0
begin
select @MsgDate=getdate()
select @strMsg=substring(description, 1, 150) from master.dbo.sysmessages where error=@Err
insert into IntLog values (@RunGroup, 'GL', @MsgDate, @strMsg)
end

The problem is that the message can be, for example:
"Line %d: Incorrect syntax near '%.*ls'."
How can I get the true message (with %d et %ls well filled).

Best regards

Nicolas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources