sql server - transacciones - Transacción de procedimiento almacenado

transaction block sql

Question

Nunca he usado Transaction, Commit y Rollback antes y ahora necesito usar uno. He comprobado algunos ejemplos en línea, etc. para asegurarme de que, de hecho, estoy usando esto correctamente, pero todavía no estoy seguro de haberlo codificado correctamente. Espero que alguien pueda revisar y aconsejarme si esto parece correcto.

Básicamente tengo 2 bases de datos para una aplicación. Uno es un archivo, lo que significa que los datos que ya no serán manipulados por los usuarios se moverán a esta base de datos. Pero en caso de que alguna vez lo necesiten, transferiré los datos necesarios a la base de datos principal para su uso. Mi proc almacenado está abajo:

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

Tengo dos inserciones que mueven los datos de 2 tablas de la base de datos de archivo. Si esas inserciones son exitosas, entonces eliminaré los datos de la base de datos de archivo. Apreciaría cualquier comentario sobre esto, debo asegurarme de que lo estoy haciendo correctamente.

Gracias

Answer 1

Bueno, reescribo rápidamente su SP utilizando el concepto TRY CATCH y la TRANSACCIÓN que solicitó, pero no lo verifiqué.

Este código funcionará en SQL 2005/2008

Déjame saber si este comentario puede ser útil para ti.

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

Answer 2

Primero, las bases de datos son bastante confiables. Y si fallan, tiene un problema más grande que manejar transacciones individuales. Por lo tanto, mi opinión es que tiene demasiados errores en la comprobación de una transacción simple. Un inserto defectuoso es un evento tan inusual que normalmente no escribiría código para manejarlo.

Segundo, este código no en realidad "captura" los errores:

IF @ErrorCode <> 0

Un error en la declaración SQL abortará el procedimiento almacenado y regresará al cliente. Tendría que intentar ... capturar para manejar realmente un error en un procedimiento almacenado.

En tercer lugar, trato de evitar raiserr . Puede hacer cosas inesperadas tanto en el servidor como en el lado del cliente. En su lugar, considere usar un parámetro de output para devolver información de error al programa cliente.