存储过程事务

Question

我以前从未使用过事务、提交和回滚，现在我需要使用它们。我查看了网上的例子，确保自己正确使用，但我仍不确定是否编码正确。我希望有人能够审核并建议我是否正确。

基本上，我的应用程序有两个数据库。其中一个是归档数据库，意味着不再由用户操作的数据将移动到此数据库中。但如果他们需要使用这些数据，我会将所需的数据移回主数据库。我的存储过程如下：

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

我有两个插入操作，将数据从存档数据库的2个表中移动。如果这些插入操作成功，则会从存档数据库中删除数据。我希望得到任何反馈，确保我做得正确。

谢谢。

- Taryn

---

哪个版本的SQL Server？这将在方法上产生很大的差异。 - HLGEM

Answer 1

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

这段代码将在SQL 2005/2008中运行。

如果这个反馈对您有用，请让我知道。

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

- Luka Milani

---

好的例子！谢谢 - Rich

我尝试使用类似上面的代码在不同的点上强制出现错误，而且完美地运行了。谢谢！ - Andrew

Answer 2

首先，数据库相当可靠。如果它们出现故障，你面临的问题比处理单个事务更大。因此，我的反馈是，对于简单的事务，你进行了过多的错误检查。插入失败是如此罕见的事件，通常不需要编写代码来处理它。

其次，这段代码实际上不会“捕获”错误：

IF @ErrorCode <> 0

如果SQL语句存在错误，存储过程将会中止并返回给客户端。您需要使用try ... catch来处理存储过程中的错误。

第三，在避免使用raiserr。它可能会对服务器和客户端产生意想不到的影响。相反，考虑使用output参数将错误信息返回给客户程序。

- Andomar