question

venkat reddy Ravu avatar image
venkat reddy Ravu asked

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

begin

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????

t-sqlstored-procedureserror-trap
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Tom for re-formatting. That is much easier to read.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Is this the entire procedure?

What version of SQL-Server are you using?

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

http://www.databasejournal.com/features/mssql/article.php/3651861/Error-handling-in-SQL-Server-2005.htm

SQL 2000 example

http://www.sommarskog.se/error-handling-II.html

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

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

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 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.
0 Likes 0 ·
I was going to reformat the code, but don't have high enough rep yet. Guess it's a good thing.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.