Rollback and Raiseerror, which first?

Question

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?

Antiworld

Depends on severity of error too. If you are raising a custom error like "there is more than one row in the result set" which isn't a server error, just an error for you, then you may want to roll back where as this would t error out in a try catch. – Bog

Answer 1

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

Retinue

As an addendum since this answer still occasionally gets an upvote: in newer versions (2016+ I think) you don't need to store the error details and reraise the error, you can simply use THROW – Retinue