sql-server-2005 - 错误发生后仍可继续执行程序

Question

以下是我在SQL Server 2005中的过程

PROCEDURE [dbo].[sp_ProjectBackup_Insert]
    @prj_id bigint
    AS
    BEGIN
     DECLARE @MSG varchar(200)
     DECLARE @TranName varchar(200)
     DECLARE @return_value int


    -- 1. Starting the transaction 
     begin transaction @TranName

    -- 2. Insert the records

     SET IDENTITY_INSERT [PMS_BACKUP].[Common].[PROJECT] ON  INSERT INTO [PMS_BACKUP].[Common].[PROJECT] ([PRJ_ID],[PRJ_NO1],[PRJ_NO2],[PRJ_NO3],[PRJ_DESC],[IS_TASKFORCE],[DATE_CREATED],[IS_APPROVED],[DATE_APPROVED],[IS_HANDEDOVER],[DATE_HANDEDOVER],[DATE_START],[DATE_FINISH],[YEAR_OF_ORDER],[CLIENT_DETAILS],[SCOPE_OF_WORK],[IS_PROPOSAL],[PRJ_MANAGER],[PRJ_NAME],[MANAGER_VALDEL],[MANAGER_CLIENT],[DEPT_ID],[locationid],[cut_off_date]) SELECT * FROM  [pms].[Common].[PROJECT]  T WHERE T.PRJ_ID =  (@prj_id) SET IDENTITY_INSERT [PMS_BACKUP].[Common].[PROJECT] OFF        IF @@ERROR <> 0 GOTO HANDLE_ERROR
      SET IDENTITY_INSERT [PMS_BACKUP].[Common].[DEPARTMENT_CAP] ON  INSERT INTO [PMS_BACKUP].[Common].[DEPARTMENT_CAP] ([CAP_ID],[DEPT_ID],[PRJ_ID],[IS_CAPPED],[DATE_CAPPED],[CAPPED_BY],[CAP_APPROVED_BY],[STATUS],[UNCAPPED_BY],[DATE_UNCAPPED],[DESCRIPTION],[UNCAP_APPROVED_BY],[LOCATIONID]) SELECT * FROM  [pms].[Common].[DEPARTMENT_CAP]  T WHERE T.PRJ_ID =  (@prj_id) SET IDENTITY_INSERT [PMS_BACKUP].[Common].[DEPARTMENT_CAP] OFF        IF @@ERROR <> 0 GOTO HANDLE_ERROR
       INSERT INTO [PMS_BACKUP].[Common].[DOC_REG]  SELECT * FROM  [pms].[Common].[DOC_REG]  T WHERE T.PRJ_ID =  (@prj_id)    IF @@ERROR <> 0 GOTO HANDLE_ERROR 



    -- 3. Commit transaction

     COMMIT TRANSACTION @TranName;

        return @@trancount;

    HANDLE_ERROR: 
     rollback transaction @TranName
     RETURN 1 
    END

问题是即使第一个插入查询失败，它也不会停止处理并恢复其余的插入查询。我得到的返回值为1，但是在结果窗口中，我可以看到这样的日志

(0 row(s) affected) Msg 2627, Level 14, State 1, Procedure sp_ProjectBackup_Insert, Line 35 Violation of PRIMARY KEY constraint 'PK_PROJECT'. Cannot insert duplicate key in object 'Common.PROJECT'. The statement has been terminated.

(0 row(s) affected)

(0 row(s) affected)

我以为 return 1将退出错误处理代码，但不会发生。我的错误处理有任何问题吗？

Answer 1

您需要对语句进行适当的错误处理。在SQL 2005及更高版本中，这意味着尝试/捕获:

PROCEDURE [dbo].[sp_ProjectBackup_Insert] 
    @prj_id bigint 
    AS 
    BEGIN 
     DECLARE @MSG varchar(200) 
     DECLARE @TranName varchar(200) 
     DECLARE @return_value int 


    -- 1. Starting the transaction  
    BEGIN TRANSACTION @TranName 

    -- 2. Insert the records 

    BEGIN TRY

         SET IDENTITY_INSERT [PMS_BACKUP].[Common].[PROJECT] ON  INSERT INTO [PMS_BACKUP].[Common].[PROJECT] ([PRJ_ID],[PRJ_NO1],[PRJ_NO2],[PRJ_NO3],[PRJ_DESC],[IS_TASKFORCE],[DATE_CREATED],[IS_APPROVED],[DATE_APPROVED],[IS_HANDEDOVER],[DATE_HANDEDOVER],[DATE_START],[DATE_FINISH],[YEAR_OF_ORDER],[CLIENT_DETAILS],[SCOPE_OF_WORK],[IS_PROPOSAL],[PRJ_MANAGER],[PRJ_NAME],[MANAGER_VALDEL],[MANAGER_CLIENT],[DEPT_ID],[locationid],[cut_off_date]) SELECT * FROM  [pms].[Common].[PROJECT]  T WHERE T.PRJ_ID =  (@prj_id) SET IDENTITY_INSERT [PMS_BACKUP].[Common].[PROJECT] OFF        IF @@ERROR <> 0 GOTO HANDLE_ERROR 
         SET IDENTITY_INSERT [PMS_BACKUP].[Common].[DEPARTMENT_CAP] ON  INSERT INTO [PMS_BACKUP].[Common].[DEPARTMENT_CAP] ([CAP_ID],[DEPT_ID],[PRJ_ID],[IS_CAPPED],[DATE_CAPPED],[CAPPED_BY],[CAP_APPROVED_BY],[STATUS],[UNCAPPED_BY],[DATE_UNCAPPED],[DESCRIPTION],[UNCAP_APPROVED_BY],[LOCATIONID]) SELECT * FROM  [pms].[Common].[DEPARTMENT_CAP]  T WHERE T.PRJ_ID =  (@prj_id) SET IDENTITY_INSERT [PMS_BACKUP].[Common].[DEPARTMENT_CAP] OFF        IF @@ERROR <> 0 GOTO HANDLE_ERROR 
           INSERT INTO [PMS_BACKUP].[Common].[DOC_REG]  SELECT * FROM  [pms].[Common].[DOC_REG]  T WHERE T.PRJ_ID =  (@prj_id)    IF @@ERROR <> 0 GOTO HANDLE_ERROR  

        -- 3. Commit transaction 

        COMMIT TRANSACTION @TranName; 
        RETURN 0

    END TRY 

    BEGIN CATCH

        --HANDLE_ERROR
        ROLLBACK TRANSACTION @TranName 
        RETURN 1  

    END CATCH 

    END 

(请务必对此进行测试和调试-应该不错，但您永远不会知道。)

RETURN值仅与所谓的过程相关，如果不检查成功或失败，则可能有问题。