我从来没有使用过交易,提交和回滚,现在我需要使用一个.我已经在网上检查了一些例子,以确保我实际上正确使用这个,但我仍然不确定我是否编码这个正确的.我希望有人可以审查并建议我,如果这似乎是正确的.
基本上我有一个应用程序的2个数据库.一个是存档 – 意味着不再被用户操纵的数据将被移动到这个数据库.但是,如果他们需要它,我将把所需的数据移回主数据库以供使用.我的存储过程如下：
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',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',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个表移动数据.如果这些插入成功,那么我将从存档数据库中删除数据.我非常感谢任何有关这方面的反馈意见,我需要确保我正确地做到这一点.
谢谢
解决方法
哦,我很快地重写你的SP使用概念TRY CATCH和TRANSACTION,如你所要求但我没有检查.
如果这种反馈对您有用,请告诉我们
CREATE PROCEDURE [dbo].[spReopenClosed]
(
@Return_Message VARCHAR(1024) = '' OUT,@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
