[SOLVED] Rollback and Raiseerror, which first?

Rollback and Raiseerror, which first?

I sometimes perform the following set of statement in the following order:

Raiseerror(...)
Rollback;

but I am wondering if it cause the same effect as below:

Rollback;
Raiseerror(...)

I understand they are the same and cause the same effect. Doing Rollback first, after execution it continues executing on the following line, that is, Raiseerrror(...)

Could anyone confirm this? or is preferable to execute this set of statements in a concret way?

Solution

It would matter if you were in a TRY-CATCH block - the raiserror would divert execution to the catch block, so if the rollback came after it (within the try block) then it would not execute.

Also it would depend on the severity of the error - severity 20+ terminates the database connection.

A nice pattern to use is something like

begin try
    begin transaction;

    -- do stuff

    commit transaction;
end try
begin catch
    declare @ErrorMessage nvarchar(max), 
        @ErrorSeverity int, 
        @ErrorState int;

    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

    if @@trancount > 0
        rollback transaction;

    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch