Error Handling with transaction

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 2005 Forums

Transact-SQL (2005)

Error Handling with transaction

Author

Topic

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-01-28 : 09:39:57

I want to create a stored procedure that whenever an error occurs it is logged into a logging table. In my code I use a table that doesn't exist for testing purposes so I will get an error. The problem is that this error isn't logged into my table. I analysed it and placed 'print test' as you can see in my code, to see whether I get into the Catch Block, but is doesn't. What am i doing wrong?

This is my code:

create procedure Test
as
set xact_abort on

begin try
begin tran Test
delete
from schema.table

update schema.table
set T1 = 'test'

commit tran Test
end try

begin catch
print 'test'

declare
@ErrorMessage varchar(300),
@ErrorSeverity smallint,
@ErrorState smallint,
@UserName varchar(50)

set @ErrorMessage = (select error_message())
set @ErrorSeverity = (select error_severity())
set @ErrorState = (select error_state())
set @UserName = (select user_name())

if (xact_state() = -1)
begin
rollback tran Test

--Logging into a Loggingtable
exec centrallogging.dbo.usp_log
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@UserName
end
if (xact_state() = 1)
begin
commit tran Test
end
end catch
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:41:33

you need raiserror from the try block based on value of @@ERROR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:42:09

also see this

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

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-01-28 : 10:03:13

@@ERROR returns the error number for the last statement. We used this in SQL Server 2000. I read that in SQL Server 2005 it isn't necessary to use @@ERROR after every single statement but that you can use begin try .. end try to capture every error within it.

My code is almost the same as in BOL:

-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;

-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
-- a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources