USE AdventureWorks; GO IFEXISTS (SELECT name FROM sys.objects WHERE name = N'SaveTranExample') DROPPROCEDURE SaveTranExample; GO CREATEPROCEDURE SaveTranExample @InputCandidateIDINT AS -- Detect if the procedure was called -- from an active transaction and save -- that for later use. -- In the procedure, @TranCounter = 0 -- means there was no active transaction -- and the procedure started one. -- @TranCounter > 0 means an active -- transaction was started before the -- procedure was called. DECLARE@TranCounterINT; SET@TranCounter=@@TRANCOUNT; IF@TranCounter>0 -- Procedure called when there is -- an active transaction. -- Create a savepoint to be able -- to roll back only the work done -- in the procedure if there is an -- error. SAVETRANSACTION ProcedureSave; ELSE -- Procedure must start its own -- transaction. BEGINTRANSACTION; -- Modify database. BEGIN TRY DELETE HumanResources.JobCandidate WHERE JobCandidateID =@InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF@TranCounter=0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMITTRANSACTION; END TRY BEGIN CATCH -- An error occurred; must determine -- which type of rollback will roll -- back only the work done in the -- procedure. IF@TranCounter=0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACKTRANSACTION; ELSE -- Transaction started before procedure -- called, do not roll back modifications -- made before the procedure was called. IF XACT_STATE() <>-1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACKTRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE@ErrorMessageNVARCHAR(4000); DECLARE@ErrorSeverityINT; DECLARE@ErrorStateINT; SELECT@ErrorMessage= ERROR_MESSAGE(); SELECT@ErrorSeverity= ERROR_SEVERITY(); SELECT@ErrorState= ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState-- State. ); END CATCH GO
posted on
2008-05-19 19:13阿牛-专注金融行业开发 阅读(
...) 评论(
...)
编辑
收藏