Catching Exception in Stored Proc and Logging

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

General SQL Server Forums

New to SQL Server Programming

Catching Exception in Stored Proc and Logging

Author

Topic

ayan_guha
Starting Member

5 Posts

Posted - 2005-12-07 : 03:43:31

Hi,

I am new to SQL Server and hence asking this.....

I have a requirement to catch any problem within my code and log it into a table with structure:

CREATE TABLE ERROR_LOG
(MSG varchar(1000),
ERROR_CODE varchar(1000)
)

As an example:

declare @test int
begin
--deliberately assigning a char into an int variable
set @test='ABC'
end

This, as expected throws an error like:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'ABC' to data type int.

I want to catch the first line to ERROR_CODE field and the second line to MSG field in ERROR_LOG table

I also need to do it such that this proc seems SUCCEDED with logging into error log

How can I do this in SQL Server?

Please suggest.......

[From Oracle background, actually I am speaking about EXCEPTION Block in Pl/SQL]

Best Regards,

Ayan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 03:51:53

Start with these
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-07 : 04:03:20

I guess for this you need to look out for the following things in Book online

@@Error
RaiseError

There is no Exception block in sql Server.. where if any errors occured in the Execution block will be traped at the exception.. ..

Sucess Comes to those who Believe in Beauty of their Dream..

ayan_guha
Starting Member

5 Posts

Posted - 2005-12-07 : 05:44:31

Thanks Guys..it really helped a lot and I came up with this:

declare @test int
declare @err varchar(400)
declare @err_msg varchar(1000)
begin try
set @test='ABC'
end try
BEGIN CATCH
select @err = str(error_number()),@err_msg = error_message()
exec dbo.FCI_ERROR_LOG_PROC @err,@err_msg,'test',1
--PRINT 'errno: ' + ltrim(str(error_number()))
-- PRINT 'errmsg: ' + error_message()
END CATCH

Now, Can you please suggest if BEGIN TRY is mandetory to use a CATCH block?
And if you experienced people can suggest anything more elegant in this regard?

Thanks a bunch.....

Ayan

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-07 : 07:48:26

what are you trying to do??

Sucess Comes to those who Believe in Beauty of their Dream..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources