Sql server: insert into a table inside a transaction to register an error?

Question

I have an SP (Stored Procedure) that is contained in some T-SQL statements .....

All T-sql statements are in the transaction block, and if any errors occur, I roll back from every thing.

like this:

BEGIN TRANSACTION ..... ..... IF @X=1 BEGIN declare cu cursor for select col1,col2 from Table1 where Id=@Y open cu fetch next from cuinto @A, @B while @@Fetch_Status = 0 BEGIN ..... ...... IF @@ERROR <>0 BEGIN ROLLBACK TRANSACTION RETURN END END ..... ..... 

Sp is not working properly, and I cannot find what it means ... I find it a good idea to register every operation in sp by inserting some data into the table. My question is:

Since it uses a transaction, each insert will be rolled back.

What is your opinion? Is there another way?

thanks

Answer 1

3 things:
1) please do not use cursors if you do not need it.
2) you can log in using RAISERROR WITH LOG or insert data into a table variable and then insert that into a real table after you collapse the transaction. This is possible because table variables are transaction independent.
3) Use a try catch block

Answer 2

There is no reason to use @@ ERROR now: TRY / CATCH is much more reliable. To understand more, I recommend reading Erland Sommarskog, "Error Handling in SQL 2005 and Later," which is one of the final related articles.

In this case, without TRY / CATCH, some errors are interrupted: this means that the code stops and the error does not fall into the trap. This is fixed using TRY / CATCH, with the exception of compilation errors.

This template is from my previous answer. Nested stored procedures containing the TRY CATCH ROLLBACK template?

 CREATE PROCEDURE [Name] AS SET XACT_ABORT, NOCOUNT ON DECLARE @starttrancount int BEGIN TRY SELECT @starttrancount = @@TRANCOUNT IF @starttrancount = 0 BEGIN TRANSACTION [...Perform work, call nested procedures...] IF @starttrancount = 0 COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() <> 0 AND @starttrancount = 0 ROLLBACK TRANSACTION RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] -- if desired INSERT ExceptionLogTable () .. END CATCH GO 

If you use SET XACT_ABORT ON (which, in my opinion, should be best practice), then in any CATCH block @@ trancount is zero. Thus, you can write to the logging table here, if you want, in addition to the error.

Answer 3

I rewrote your code to give you a real example using Transaction and Try Catch

 CREATE PROCEDURE [dbo].[mySP] ( @X int, @Y int, @Return_Message VARCHAR(1024) = '' OUT ) AS SET NOCOUNT ON; Declare @A varchar(100) @B varchar(100) BEGIN TRY BEGIN TRAN IF @X=1 BEGIN declare cu cursor for select col1,col2 from Table1 where Id=@Y open cu fetch next from cu into @A, @B while @@Fetch_Status = 0 BEGIN -- ..... -- do your stuff FETCH NEXT FROM cu into @A, @B END END COMMIT TRAN SELECT @Return_Message = 'All OK' /************************************* * Return from the Stored Procedure *************************************/ RETURN 1 -- success END TRY BEGIN CATCH /************************************* * if errors rollback *************************************/ IF @@TRANCOUNT > 0 ROLLBACK SELECT @Return_Message = @ErrorStep + ' ' + cast(ERROR_NUMBER() as varchar(20)) + ' line: ' + cast(ERROR_LINE() as varchar(20)) + ' ' + ERROR_MESSAGE() + ' > ' + ERROR_PROCEDURE() /************************************* * Return from the Stored Procedure *************************************/ RETURN 0 -- fail END CATCH 

Using SP:

 declare @ret int, @Return_Message VARCHAR(1024) EXEC @ret = mySP 1, 2, @Return_Message OUTPUT -- the SP Fail so print or store the return message with errors ... if @ret = 0 print @Return_Message 

Answer 4

You can also implement Exception Handling with Try Catch as well

 Begin Try BEGIN TRANSACTION ..... ..... IF @X=1 BEGIN declare cu cursor for select col1,col2 from Table1 where Id=@Y open cu fetch next from cuinto @A, @B while @@Fetch_Status = 0 BEGIN ..... ...... //Your insert statement.... END ..... ..... Commit Tran End Try Begin Catch Rollback Tran DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return -- error information about the original error that -- caused execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); End Catch