By Devasta


2014-04-14 09:50:09 8 Comments

I'm writing a script that will delete records from a number of tables, but before it deletes it must return a count for a user to confirm before committing.

This is a summary of the script.

BEGIN TRANSACTION SCHEDULEDELETE
    BEGIN TRY
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        DELETE   -- delete commands full SQL cut out
        PRINT 'X rows deleted. Please commit or rollback.' --calculation cut out.
    END TRY
    BEGIN CATCH 
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage

            ROLLBACK TRANSACTION SCHEDULEDELETE
            PRINT 'Error detected, all changes reversed.'
    END CATCH

--COMMIT TRANSACTION SCHEDULEDELETE --Run this if count correct.

--ROLLBACK TRANSACTION SCHEDULEDELETE --Run this if there is any doubt whatsoever.

This is my first time writing transaction, is it correct/best practice to have the TRY/CATCH block inside the transaction or should the transaction be inside the TRY block?

The important factor in this script is that the user must manually commit the transaction.

3 comments

@Sudhanshu Mishra 2015-06-08 23:48:49

In addition to the good advice by M.Ali and dean above, a little bit help for those looking to use the new(er) TRY CATCH THROW paradigm in SQL SERVER:

(I couldn't easily find the complete syntax, so adding it here)

GIST : HERE

Sample stored procedure code here (from my gist):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[pr_ins_test]
@CompanyID INT
AS

SET NOCOUNT ON

BEGIN

    DECLARE @PreviousConfigID INT

    BEGIN TRY
        BEGIN TRANSACTION MYTRAN; -- Give the transaction a name
        SELECT 1/0  -- Generates divide by zero error causing control to jump into catch

        PRINT '>> COMMITING'
        COMMIT TRANSACTION MYTRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN 
            PRINT '>> ROLLING BACK'
            ROLLBACK TRANSACTION MYTRAN; -- The semi-colon is required (at least in SQL 2012)


        END
        THROW
    END CATCH
END

@Mark G 2018-04-30 22:19:16

Just an observation - The THROW should be outside of the BEGIN/END so it throws regardless of a transaction being open.

@Sudhanshu Mishra 2018-05-02 07:03:37

@MarkG not sure I understand, if the THROW is outside of BEGIN/END Catch, executing this stored proc would always throw an exception, unless you follow the commit tran with a return statement. As the excerpt stands now, just moving the throw outside would be useless

@BrianB 2018-05-03 14:06:19

I suspect @MarkG is saying that the THROW should be after the END but before the END CATCH. This would ensure that the exception is thrown irrespective of @@TRANCOUNT.

@Sudhanshu Mishra 2018-05-07 07:00:41

@BrianB makes sense, edited.

@Rich 2019-05-23 04:38:13

I tried to add the THROW statement within a database project in Visual studio and get an error.

@M.Ali 2014-04-14 10:22:49

Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions.

If something goes wrong while you are in the TRY block and you have opened a transaction, the control will jump to the CATCH block. Simply rollback your transaction there and do other error handling as required.

I have added a little check for any open transaction using @@TRANCOUNT function before actually rolling back the transaction. It doesn't really make much sense in this scenario. It is more useful when you are doing some validations checks in your TRY block before you open a transaction like checking param values and other stuff and raising error in the TRY block if any of the validation checks fail. In that case, the control will jump to the CATCH block without even opening a transaction. There you can check for any open transaction and rollback if there are any open ones. In your case, you really don't need to check for any open transaction as you will not enter the CATCH block unless something goes wrong inside your transaction.

Do not ask after you have executed the DELETE operation whether it needs to be committed or rolled back; do all these validation before opening the transaction. Once a transaction is opened, commit it straightaway and in case of any errors, do error handling (you are doing a good job by getting detailed info by using almost all of the error functions).

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

@dean 2014-04-14 10:23:59

Never wait for an end user to commit the transaction, unless it's a single-user mode database.

In short, it's about blocking. Your transaction will take some exclusive locks on resources being updated, and will hold on to those lock untill the transaction is ended (committed or rolled back). Nobody will be able to touch those rows. There are some different problems if snapshot isolation is used with version store cleanup.

Better to first issue a select query to determine a number of qualifying rows, present this to the end user, and after he confirms do the actual delete.

Related Questions

Sponsored Content

5 Answered Questions

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] error handling inside a while loop

2 Answered Questions

0 Answered Questions

SET_XACT_ABORT ON in a transaction with rollback present in catch block

1 Answered Questions

[SOLVED] SQL transaction in try... catch will not roll back

1 Answered Questions

Sponsored Content