Transaction de procédure stockée

Je n'ai jamais utilisé une Transaction, Commit et Rollback avant et maintenant j'ai besoin d'en utiliser un. J'ai vérifié autour en ligne, etc pour des exemples pour s'assurer que je suis en fait en utilisant correctement mais je ne sais pas encore si j'ai codé ce correct. J'espère que quelqu'un pourra revoir et de m'informer si cela semble correct.

Fondamentalement, j'ai 2 bases de données pour une application. L'un est une archive - sens des données qui ne va plus être manipulés par les utilisateurs seront déplacés vers la bd. Mais dans le cas où jamais ils en ont besoin, je vais déplacer les données nécessaires à la base de données pour les utiliser. Ma procédure stockée est ci-dessous:

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

J'ai deux insertions de déplacer les données à partir de 2 tables de la base de données d'Archive. Si ces inserts sont couronnées de succès, alors je vais supprimer les données de l'Archive DB. J'apprécierais vos commentaires sur ce, j'ai besoin de m'assurer que je suis en train de faire cela correctement.

Grâce

source d'informationauteur Taryn

  1. 29

    Oh eh bien, je réécrire rapidement votre SP à l'aide de la notion ESSAYER de les ATTRAPER et de la TRANSACTION comme vous l'avez demandé, mais je n'ai pas vérifier.

    Ce code fonctionnera dans SQL 2005/2008

    Laissez-moi savoir si ce retour d'expérience peut être utile pour vous

    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
  2. 3

    D'abord, les bases de données sont relativement fiables. Et si elles échouent, vous avez un gros problème que sur le traitement des transactions individuelles. Donc, mes commentaires, que vous avez trop de vérification des erreurs pour une simple transaction. Un défaut insert est un événement inhabituel que vous le feriez normalement pas écrire du code pour gérer.

    Seconde, ce code ne fait "attraper" des erreurs:

    IF @ErrorCode <> 0

    Une erreur dans l'instruction SQL pour abandonner la procédure stockée et de retour au client. Vous auriez à try ... catch à fait gérer une erreur dans une procédure stockée.

    Troisième, j'essaie d'éviter raiserr. Il peut faire des choses inattendues à la fois sur le serveur et côté client. Au lieu de cela, pensez à utiliser un output paramètre pour renvoyer des informations d'erreur le programme client.