存储过程事务

本文介绍了存储过程事务的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我以前从未使用过事务，提交和回滚，现在我需要使用一个。我已经在网上等地方进行了检查，以获取示例，以确保我实际上正确地使用了此功能，但仍不确定是否已正确编码了该代码。我希望有人可以复查并建议我是否正确。

I have never used a Transaction, Commit and Rollback before and now I need to use one. I have checked around online, etc for examples to make sure that I am in fact using this correctly but I am still not sure if I have coded this correct. I am hoping someone can review and advise me if this seems correct.

基本上我有2个用于应用程序的数据库。一个是存档-意味着不再将由用户操纵的数据将被移至该DB。但是，如果他们有需要，我将需要的数据移回主数据库以供使用。我的存储过程如下：

Basically I have 2 databases for an application. One is an archive - meaning data that is no longer going to be manipulated by the users will be moved to this DB. But in the event they ever need it, I will move the needed data back to the main database for use. My stored proc is below:

CREATE PROCEDURE [dbo].[spReopenClosed] ( @Return_Message VARCHAR(1024) = '' OUT, @IID uniqueidentifier, @OpenDate smalldatetime, @ReopenedBy uniqueidentifier ) AS BEGIN SET NOCOUNT ON; /****************************** * Variable Declarations *******************************/ DECLARE @ErrorCode int /****************************** * Initialize Variables *******************************/ SELECT @ErrorCode = @@ERROR IF @ErrorCode = 0 BEGIN TRANSACTION /**************************************************************************** * Step 1 * Copy the Closed from the Archive ****************************************************************************/ INSERT INTO OPS.dbo.SM_T_In SELECT FROM OPS_ARCHIVE.Archive.SM_T_In W WHERE W.GUID = @IID AND W.OpenDate = @OpenDate IF @ErrorCode <> 0 BEGIN -- Rollback the Transaction ROLLBACK RAISERROR ('Error in Copying from the archive', 16, 1) RETURN END /**************************************************************************** * Step 2 * copy the notes ****************************************************************************/ INSERT INTO OPS.dbo.SM_T_Notes SELECT FROM OPS_ARCHIVE.Archive.SM_T_Notes W WHERE W.GUID = @IID IF @ErrorCode <> 0 BEGIN -- Rollback the Transaction ROLLBACK RAISERROR ('Error in copying the notes', 16, 1) RETURN END /**************************************************************************** * Step 3 * Delete the from the Archive - this will also delete the notes ****************************************************************************/ DELETE FROM OPS_ARCHIVE.Archive.SM_T_In WHERE OPS_ARCHIVE.Archive.SM_T_In.GUID = @IID IF @ErrorCode <> 0 BEGIN -- Rollback the Transaction ROLLBACK RAISERROR ('Error in deleting the items from the Archive', 16, 1) RETURN END COMMIT BEGIN SELECT @ErrorCode = @@ERROR IF @ErrorCode = 0 SELECT @Return_Message = 'All data was moved over' END /************************************* * Get the Error Message for @@Error *************************************/ IF @ErrorCode <> 0 BEGIN SELECT @Return_Message = [Description] -- Return the SQL Server error FROM master.dbo.SYSMESSAGES WHERE error = @ErrorCode END /************************************* * Return from the Stored Procedure *************************************/ RETURN @ErrorCode -- =0 if success, <>0 if failure END

我有两个插入物，用于从Archive数据库的2个表中移动数据。如果插入成功，那么我将从存档数据库中删除数据。对于这个问题，我将不胜感激，需要确保自己正确执行此操作。

I have two inserts that move the data from 2 tables from the Archive database. If those inserts are successful, then I will delete the data from the Archive DB. I would appreciate any feedback on this, I need to make sure that I am doing this properly.

谢谢

推荐答案

好吧，我根据您的要求使用 TRY CATCH和TRANSACTION 概念快速重写了您的SP，但我没有检查它。

Oh well i rewrite quickly your SP using the concept TRY CATCH and the TRANSACTION as you requested but i didnt check it.

此代码将在 SQL 2005/2008

让我知道此反馈是否对您有用

Let me know if this feedback can be useful for you

CREATE PROCEDURE [dbo].[spReopenClosed] ( @Return_Message VARCHAR(1024) = '' OUT, @IID uniqueidentifier, @OpenDate smalldatetime, @ReopenedBy uniqueidentifier ) AS SET NOCOUNT ON; /****************************** * Variable Declarations *******************************/ DECLARE @ErrorCode int DECLARE @ErrorStep varchar(200) /****************************** * Initialize Variables *******************************/ SELECT @ErrorCode = @@ERROR BEGIN TRY BEGIN TRAN /**************************************************************************** * Step 1 * Copy the Closed from the Archive ****************************************************************************/ SELECT @ErrorStep = 'Error in Copying from the archive'; INSERT INTO OPS.dbo.SM_T_In SELECT * FROM OPS_ARCHIVE.Archive.SM_T_In WHERE GUID = @IID AND W.OpenDate = @OpenDate /**************************************************************************** * Step 2 * copy the notes ****************************************************************************/ SELECT @ErrorStep = 'Error in copying the notes' INSERT INTO OPS.dbo.SM_T_Notes SELECT * FROM OPS_ARCHIVE.Archive.SM_T_Notes WHERE GUID = @IID /**************************************************************************** * Step 3 * Delete the from the Archive - this will also delete the notes ****************************************************************************/ SELECT @ErrorStep = 'Error in deleting the items from the Archive' DELETE FROM OPS_ARCHIVE.Archive.SM_T_In WHERE OPS_ARCHIVE.Archive.SM_T_In.GUID = @IID COMMIT TRAN SELECT @ErrorCode = 0, @Return_Message = 'All data was moved over' /************************************* * Return from the Stored Procedure *************************************/ RETURN @ErrorCode -- =0 if success, <>0 if failure END TRY BEGIN CATCH /************************************* * Get the Error Message for @@Error *************************************/ IF @@TRANCOUNT > 0 ROLLBACK SELECT @ErrorCode = ERROR_NUMBER() , @Return_Message = @ErrorStep + ' ' + cast(ERROR_NUMBER() as varchar(20)) + ' line: ' + cast(ERROR_LINE() as varchar(20)) + ' ' + ERROR_MESSAGE() + ' > ' + ERROR_PROCEDURE() /************************************* * Return from the Stored Procedure *************************************/ RETURN @ErrorCode -- =0 if success, <>0 if failure END CATCH