Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to commit and rollback transaction in sql server?

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..

like image 348
Vishal Avatar asked Oct 14 '10 17:10

Vishal


People also ask

How do I commit a transaction in SQL Server?

Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications since the start of the transaction a permanent part of the database, frees the transaction's resources, and decrements @@TRANCOUNT to 0.

Can we rollback transaction after commit in SQL Server?

ROLLBACK is the SQL command that is used for reverting changes performed by a transaction. When a ROLLBACK command is issued it reverts all the changes since last COMMIT or ROLLBACK.


1 Answers

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 > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end
like image 199
Remus Rusanu Avatar answered Oct 29 '22 14:10

Remus Rusanu