Error logic check for Rollback/Commit in the script

Kenny Gua 411 Reputation points
2022-10-28T13:40:27.283+00:00

Hi, Is the following error check sentences are correct? In some cases this error logic didn't work and executed the command.

One more scenario: Suppose I have 10 updates in my script with error check and if I have error in any line then script should not execute and all changes should be rollback with no commit.

So just want to make sure that following error checks are OK if i implement in the script.

Begin Tran
Declare @errorchk AS Integer
SET @errorchk=0

Delete from Num where st='AA' IF @@ERROR>0 SET @errorchk=@errorchk+1

Print @errorchk=0
Commit
Print 'All actions executed successfully'
END
ELSE

BEGIN
Rollback
Print 'All actions have been Rollback'
END
Go

If @@Trancount>0
Print 'Critical Error--Investigate'
Rollback
End

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 15,101 Reputation points
    2022-10-28T15:01:19.347+00:00

    Good day ,

    Your code (as it looks now) includes multiple issues.

    With that said, first of all, please use a code block when you present a code, other wise the system might display the code wrongly.

    To do this, (1) mark the entire code in the editor, and (2) click the button "Code Sample" or simply click: Ctrl + K

    ----------

    Back to the question

    Print @errorchk=0

    This make no sense. Do you want to print the value or set a new value? This line is not legit

    You can have Print @errorchk or you can have SET @errorchk=0

    IF @@ERROR>0 SET @errorchk = @errorchk+1 ... ELSE

    Your IF condition is inside a block of transaction. What is the meaning of an ELSE which is not in the same scope?

    END

    What do you try to end if you did not begin anything?!?

    I highly (double this "HIGHLY") recommend you to learn in an orderly manner. People usually waste a lot more time in attempts of jumping from one topic to the other, instead of learning something fully that should not take more than a few days.

    My recommendation is to go over the following tutorial (or any other full tutorial) from start to end: https://www.tutorialspoint.com/sql/index.htm

    If you do not accept my recommendation and want o jump from topic to topic then:

    (1) Check the Syntax of using IF/ELSE conditions here:
    https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

    (2) Check the Syntax of using Transaction
    https://learn.microsoft.com/sql/t-sql/language-elements/begin-end-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

    (3) Check the Syntex of using blocks of BEGIN...END clock
    https://learn.microsoft.com/en-us/sql/t-sql/language-elements/end-begin-end-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

    ----------

    My guess is that you meant to execute the COMMIT only of the IF condition is true, but your IF condition has only one statement, unless you start a block using BEGIN. You do have an END so I am guessing that this should be the END of what execute under IF. This mean that you are missing the BEGIN command.

    Another huge issue is that it seems like you execute COMMIT when the condition IF @@ERROR>0 and you execute the ROLLBACK if the not. IT should be vise Vera. If you have error then you want to ROLLBACK and if all OK then you COMMIT the transaction

    Check if this is what you meant :-)

    BEGIN TRY  
        BEGIN TRANSACTION  
      
            -- Run your code here for example  
     Delete from Num where id=1   
      
        COMMIT TRAN -- Transaction Success!  
    END TRY  
    BEGIN CATCH  
        IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack in case of Error  
      
        -- Raise error messages as follows:  
        DECLARE @ErrorMessage NVARCHAR(4000);    
        DECLARE @ErrorSeverity INT;    
        DECLARE @ErrorState INT;    
      
        -- You can run commands in case of an error here  
     SELECT 'ISSSUE'  
      
     -- And raise the error  
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
      
    END CATCH  
    

    Note! Check the following document for more understanding
    https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175523(v=sql.105)?redirectedfrom=MSDN&WT.mc_id=DP-MVP-5001699

    If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful