How to commit and rollback transaction in sql server

Question

I have a huge script for creating tables and porting data from one server  So this sceipt basically has -    Create statements for tables  Insert for porting the data to these newly created tables  Create statements for stored procedures     So I have this code but it does not work basically   ERROR is always zero I think    BEGIN TRANSACTION --CREATES --INSERTS --STORED PROCEDURES CREATES     -- ON ERROR ROLLBACK ELSE COMMIT THE TRANSACTION     IF   ERROR    0         BEGIN              PRINT   ERROR                       PRINT  ERROR IN SCRIPT              ROLLBACK TRANSACTION             RETURN         END     ELSE     BEGIN         COMMIT TRANSACTION         PRINT  COMMITTED SUCCESSFULLY      END     GO   Can anyone help me write a transaction which will basically rollback on error and commit if everything is fine  Can I use RaiseError somehow here

User · Answer

As per http   msdn microsoft com en-us library ms188790 aspx    ERROR  Returns the error number for the last Transact-SQL statement executed   You will have to check after each statement in order to perform the rollback and return   Commit can be at the end   HTH

User · Answer

Don t use   ERROR  use BEGIN TRY BEGIN CATCH instead  See this article  Exception handling and nested transactions for a sample procedure   create procedure  usp my procedure name  as begin     set nocount on      declare  trancount int      set  trancount     trancount      begin try         if  trancount   0             begin transaction         else             save transaction usp my procedure name           -- Do the actual work here  lbexit          if  trancount   0                commit      end try     begin catch         declare  error int   message varchar 4000    xstate int          select  error   ERROR NUMBER     message   ERROR MESSAGE     xstate   XACT STATE            if  xstate   -1             rollback          if  xstate   1 and  trancount   0             rollback         if  xstate   1 and  trancount  gt  0             rollback transaction usp my procedure name           raiserror   usp my procedure name   d   s   16  1   error   message            return      end catch    end

User · Answer

Avoid direct references to    ERROR   It s a flighty little thing that can be lost   Declare  ErrorCode int      perform stuff     Set  ErrorCode     ERROR      other stuff     if  ErrorCode

[sql-server-2008] How to commit and rollback transaction in sql server?

Examples related to sql-server-2008

Examples related to transactions