Heim  >  Artikel  >  Datenbank  >  SQLServer 异常捕获,回滚,再抛出

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

WBOY
WBOYOriginal
2016-06-07 15:34:41996Durchsuche

一个存储过程中多个更新操作, 后面的更新操作出现异常,如果 不手动回滚 前面修改的数据是不会自动撤销的! 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;

 

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn