Geek Answers Handbook

How to rollback transaction when testing using tSQLt

I recently called a procedure containing rasierror in code. Raiserror was in a try catch block. Also BEGIN TRAN was in the same try catch block after raiserror. The Catch block is for a ROLLBACK transaction if an error occurs in the transaction. The way this is done is to check @@ TRANCOUNT if it is greater than 0. I know that he started the transaction and he needs ROLLBACK. When testing with tSQLt @@ TRANCOUNT always> 0, so if it ever falls into the CATCH block, ROLLBACK is executed and tSQLt fails (because tSQLt works in a transaction). When I run the error and the CATCH block starts, tSQLt always fails. I have no way to verify that raiserror handling is correct. How to create a test case that could potentially rollback a transaction?

+4
source share
4 answers

As you mentioned, tSQLt runs each test in its own transaction. To keep track of what is happening, it is believed that the same transaction will remain open when the test is completed. SQL Server does not support nested transactions, so your procedure rolls back everything, including status information saved for the current test. At this point, tSQLt can only assume that something really bad has happened. Therefore, it is flagged as an error.

SQL Server itself denies rollback within the procedure, causing an error if this procedure was called as part of an open transaction. For ways to handle this situation and some additional information, visit my blog on how to roll back in procedures .

+8
source

As I just read on tSQLt, this was one of the first questions that came to mind when I found out that every test runs in transactions. As some of my stored procedures start a transaction, some even use nested transactions, this can be a daunting task. What I learned about nested transactions, if you apply the following rules, you can keep your code clean from constant error checking and still handle errors gracefully.

  • Always use a TRY / CATCH block when opening transactions.
  • Always make transactions if no error has been raised.
  • Always roll back a transaction when an error occurs if @@ TRANCOUNT = 0
  • Always repeat the error if you are not sure that the transaction did not open at the beginning of the stored procedure.

Saving these rules in this case is an example of the proc implementation and test code to test it.

ALTER PROC testProc @IshouldFail BIT AS BEGIN TRY BEGIN TRAN IF @IshouldFail = 1 RAISERROR('failure', 16, 1); COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; -- Do some exception handling -- You'll need to reraise the error to prevent exceptions about inconsistent -- @@TRANCOUNT before / after execution of the stored proc. RAISERROR('failure', 16, 1); END CATCH GO --EXEC tSQLt.NewTestClass 'tSQLt.experiments'; --GO ALTER PROCEDURE [tSQLt.experiments].[test testProc nested transaction fails] AS BEGIN --Assemble DECLARE @CatchWasHit CHAR(1) = 'N'; --Act BEGIN TRY EXEC dbo.testProc 1 END TRY BEGIN CATCH IF @@TRANCOUNT = 0 BEGIN TRAN --reopen an transaction SET @CatchWasHit = 'Y'; END CATCH --Assert EXEC tSQLt.AssertEqualsString @Expected = N'Y', @Actual = @CatchWasHit, @Message = N'Exception was expected' END; GO ALTER PROCEDURE [tSQLt.experiments].[test testProc nested transaction succeeds] AS BEGIN --Act EXEC dbo.testProc 0 END; GO EXEC tSQLt.Run @TestName = N'tSQLt.experiments'
+2
source

It is better to use a BEGIN TRY block after a BEGIN TRANSACTION . I did this when I had a similar problem. This is more logical because in the CATCH block I checked IF @@TRANCOUNT > 0 ROLLBACK . This condition does not need to be checked if another error occurs before BEGIN TRANSACTION . And in this case, you can test your RAISERROR functionality.

0
source

+1 for both of the above answers.

However, if you do not want to use TRY .. CATCH, try the following code. The part between the lines ----- is a test, and above and below that tSQLt represents, before and after it calls your test. As you can see, the transaction started by tSQLt before invoking the test still exists, as expected, regardless of whether an error occurs. @@ TRANSCOUNT is still 1

You can comment on RAISERROR to try it without exception.

 SET NOCOUNT ON BEGIN TRANSACTION -- DONE BY tSQLt PRINT 'Inside tSQLt before calling the test: @@TRANCOUNT = ' + CONVERT (VARCHAR, @@TRANCOUNT) --------------------------------- PRINT ' Start of test ---------------------------' SAVE TRANSACTION Savepoint PRINT ' Inside the test: @@TRANCOUNT = ' + CONVERT (VARCHAR, @@TRANCOUNT) BEGIN TRANSACTION -- PART OF THE TEST PRINT ' Transaction in the test: @@TRANCOUNT = ' + CONVERT (VARCHAR, @@TRANCOUNT) RAISERROR ('A very nice error', 16, 0) PRINT ' @@ERROR = ' + CONVERT(VARCHAR,@@ERROR) -- PART OF THE TEST - CLEAN-UP IF @@ERROR <> 0 ROLLBACK TRANSACTION Savepoint -- Not all the way, just tothe save point ELSE COMMIT TRANSACTION PRINT ' About to finish the test: @@TRANCOUNT = ' + CONVERT (VARCHAR, @@TRANCOUNT) PRINT ' End of test ---------------------------' --------------------------------- ROLLBACK TRANSACTION -- DONE BY tSQLt PRINT 'Inside tSQLt after finishing the test: @@TRANCOUNT = ' + CONVERT (VARCHAR, @@TRANCOUNT)

With confirmation of information and code at http://www.blackwasp.co.uk/SQLSavepoints.aspx

0
source

More articles:


All Articles