Capturing the Error Description in a stored procedure

Hi,

I have a few stored procedure which will be executed one after another. If any error occurs, i need capture the Error number and ERROR DESCRIPTION and SAVE it into a table within stored procedure itself. Any idea how to do it?

I saw a similar problem from http://www.sqlservercentral.com/columnists/ajethva/capturingtheerrordescriptioninastoredprocedure.asp but i cannot download the sample code.

i want to CAPTURE the following msg :

e.g. Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

It would be great if you could send sample code.

thanks.

rama

If you use SQL server 2000, you can use the following batch. But you can’t suppers the error message.

Code Snippet

Create table ErrorLog

(

Source nvarchar(100),

CalledUser varchar(100),

ErrorNumber int,

ErrorDescription nvarchar(2000),

ErrorDatetime datetime

)

Go

Create proc MyProc

@.I as int

as

Declare @.Error as int

Select 1/@.I

Set @.Error = @.@.ERROR

If @.Error <> 0

Begin

Insert Into ErrorLog

select

object_name(@.@.PROCID),

suser_sname(),

@.Error,

description,

getdate()

from

Sysmessages

Where

error=@.Error

and msglangid = (select msglangid from syslanguages where name='us_english') -- You can change to your local language

End

Go

Exec MyProc 1

Go

Exec MyProc 0

Go

select * from ErrorLog