How to commit and rollback transaction in sql server?

Viewed 148296

I have a huge script for creating tables and porting data from one server. So this sceipt basically has -

  1. Create statements for tables.
  2. Insert for porting the data to these newly created tables.
  3. 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..

3 Answers
Related