SQLServer 异常捕获，回滚，再抛出

一个存储过程中多个更新操作， 后面的更新操作出现异常，如果 不手动回滚 前面修改的数据是不会自动撤销的！ BEGIN TRY BEGIN TRAN -- ..... COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN DECLARE @ErrorMessag NVARCHAR ( 255 ) SELECT @ErrorMessag = E

一个存储过程中多个更新操作，后面的更新操作出现异常，如果不手动回滚前面修改的数据是不会自动撤销的！

<span>BEGIN</span><span> TRY
    </span><span>BEGIN</span> <span>TRAN</span>
<span>--</span><span> .....</span>
    <span>COMMIT</span> <span>TRAN</span>
<span>END</span><span> TRY

</span><span>BEGIN</span><span> CATCH
    </span><span>ROLLBACK</span> <span>TRAN</span>

    <span>DECLARE</span> <span>@ErrorMessag</span> <span>NVARCHAR</span>(<span>255</span><span>)
    </span><span>SELECT</span> <span>@ErrorMessag</span> <span>=</span><span> Error_message()
    </span><span>RAISERROR</span> (<span>15600</span>,<span>-</span><span>1</span>,<span>-</span><span>1</span>,<span>@ErrorMessag</span><span>);
</span><span>END</span> CATCH

 

另参考： http://msdn.microsoft.com/zh-cn/library/ms178592.aspx

  

<span>BEGIN</span><span> TRY
    </span><span>--</span><span> RAISERROR with severity 11-19 will cause execution to </span>
    <span>--</span><span> jump to the CATCH block.</span>
    <span>RAISERROR</span> (<span>'</span><span>Error raised in TRY block.</span><span>'</span>, <span>--</span><span> Message text.</span>
               <span>16</span>, <span>--</span><span> Severity.</span>
               <span>1</span> <span>--</span><span> State.</span>
<span>               );
</span><span>END</span><span> TRY
</span><span>BEGIN</span><span> CATCH
    </span><span>DECLARE</span> <span>@ErrorMessage</span> <span>NVARCHAR</span>(<span>4000</span><span>);
    </span><span>DECLARE</span> <span>@ErrorSeverity</span> <span>INT</span><span>;
    </span><span>DECLARE</span> <span>@ErrorState</span> <span>INT</span><span>;

    </span><span>SELECT</span> 
        <span>@ErrorMessage</span> <span>=</span><span> ERROR_MESSAGE(),
        </span><span>@ErrorSeverity</span> <span>=</span><span> ERROR_SEVERITY(),
        </span><span>@ErrorState</span> <span>=</span><span> ERROR_STATE();

    </span><span>--</span><span> Use RAISERROR inside the CATCH block to return error</span>
    <span>--</span><span> information about the original error that caused</span>
    <span>--</span><span> execution to jump to the CATCH block.</span>
    <span>RAISERROR</span> (<span>@ErrorMessage</span>, <span>--</span><span> Message text.</span>
               <span>@ErrorSeverity</span>, <span>--</span><span> Severity.</span>
               <span>@ErrorState</span> <span>--</span><span> State.</span>
<span>               );
</span><span>END</span> CATCH;