0

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;
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KentZhou
  • 24,805
  • 41
  • 134
  • 200
  • Yes, it is possible. Have a look at [this msdn article](http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx). – Alberto De Caro Jul 11 '12 at 13:31
  • @KentZhou - you should also upvote answers that best addresses your needs. –  Jul 16 '12 at 15:59

2 Answers2

3

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

 BEGIN TRY
    -- ....
 END TRY
 BEGIN CATCH
    -- ....
 END CATCH
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • 3
    And don't just rollback, return the error to the user and even better log it in an error table. If you put information about the error into a table variable, that does not get rolled back and you can then insert to a table. Having the error information stored is invaluable in figuring out what went wrong. – HLGEM Jul 11 '12 at 14:17
0

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
Community
  • 1
  • 1