Exception handling in T-SQL,need help..

hi,I have created 2 tables as below...

CREATE TABLE dbo.sales ( orderno int, ordername varchar(50), cost float );

CREATE TABLE dbo.Error_log ( LogDate datetime NULL, source varchar(50), ErrMsg nvarchar(50), remarks varchar(50) );

Then i have created a procedure dbo.sp_sales_insert to insert records into sales table and incase any exception i need to have log the exception in Error_Log table.

create procedure dbo.sp_sales_insert ( @orderno int, @ordername varchar(20), @cost float,

) as


declare @Error int

begin transaction

insert into sales (orderno,ordername,cost) values (@orderno,@ordername,@cost)

set @Error = @@ERROR if @Error <> 0 begin goto Log_Error end commit transaction goto ProcEnd

Log_Error: rollback transaction

declare @ErrMsg varchar(1000) select @ErrMsg = [description] from master.dbo.sysmessages where error = @Error insert into error_log (LogDate,Source,ErrMsg) values (getdate(),'sp_sales_insert',@ErrMsg)

ProcEnd: end GO

i have excecuted the above procedure as below

exec sp_sales_insert 150,'abc',4000.50 ---->executed

exec sp_emp_insert 'xyz','ccc',5000.00 ----->not excecuted

im not able to capture the exception in the error_log in the second case.can anybody give best examples to capture the exception in Error_Log table????

more ▼

asked Mar 12, 2010 at 02:29 AM in Default

avatar image

venkat reddy Ravu
59 3 3 5

Thanks Tom for re-formatting. That is much easier to read.

Mar 12, 2010 at 03:28 AM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Is this the entire procedure?

What version of SQL-Server are you using?

If SQL 2005, I recommend using a try..catch block instead.


SQL 2000 example


INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
  RAISERROR ('An error occured updating the NonFatal table',10,1)

I think your goto statements might be conflicting with the error logging.

more ▼

answered Mar 12, 2010 at 03:18 AM

avatar image

5.5k 11 13 20

  • Good answer. I was going to answer to suggest try...catch if using 2005 or higher, but by the time I finished reformatting the code in the question, you had already answered.

Mar 12, 2010 at 03:24 AM Tom Staab ♦

I was going to reformat the code, but don't have high enough rep yet. Guess it's a good thing.

Mar 12, 2010 at 03:27 AM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

Your sp_sales_insert looks good to me, although you should look at TRY CATCH if you're on SQL 2005 or 2008. So sp_emp_insert must have something wrong. Can you add the code for that proc to your post?

more ▼

answered Mar 12, 2010 at 05:15 AM

avatar image

David Wimbush
10.7k 31 34 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 12, 2010 at 02:29 AM

Seen: 1763 times

Last Updated: Mar 12, 2010 at 03:21 AM

Copyright 2018 Redgate Software. Privacy Policy