Error logic check for Rollback/Commit in the script

Question

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

Answer 1

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.