CODEWITHSUNDEEP
sqlsql-server-2008t-sql
KentZhou
KentZhou

Reputation: 25573

How to catch error for a transaction in T-SQL for SQL Server?

In SQL Server, you need to check error for each SQL statement. For example if I have 3 updates in one transaction, I need some code like:

declare @HasError int
begin tran
Update tab1 set ....
Set  @HasError = @@error;
Update tab2 set ...
Set  @HasError = @@error;
Update tab3 set ...
Set  @HasError = @@error;

If @HasError<>0
  rollback tran;
else
 commit tran;

Any other solution for this case with more simple code? for instance, something like c# style:

   begin tran
    try
    {
      Update tab1 set ....    
      Update tab2 set ...
      Update tab3 set ...
      commit tran;
    }catch(error){
       rollback tran;
    }

Upvotes: 0

Views: 5002

Answers (2)

user1499112
user1499112

Reputation:

Click here to see => Why do I really need to use SET XACT_ABORT ON ?

Click here to see How SET NOCOUNT ON Improves SQL Server Stored Procedure Performance

Begin Try
    SET NOCOUNT ON
    Set XACT_ABORT ON
    Begin Tran
        --Your Query
    Commit Tran
End Try

Begin Catch
    RollBack Tran
    Select ERROR_MESSAGE() as Msg, ERROR_NUMBER() as Num
End Catch

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You can use a try catch syntax as you would in c#

 BEGIN TRY
    -- ....
 END TRY
 BEGIN CATCH
    -- ....
 END CATCH

Upvotes: 4

Related Questions