存储过程事务 [英] Stored Procedure Transaction
问题描述
我以前从未使用过事务，提交和回滚，现在我需要使用一个。我已经在网上等地方进行了检查，以获取示例，以确保我实际上正确地使用了此功能，但仍不确定是否已正确编码了该代码。我希望有人可以复查并建议我是否正确。
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
这篇关于存储过程事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
