Stored Procedure Transaction
Solution 1
Oh well i rewrite quickly your SP using the concept TRY CATCH and the TRANSACTION as you requested but i didnt check it.
This code will work in 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
Solution 2
First, databases are fairly reliable. And if they fail, you have a bigger problem than handling individual transactions. So my feedback would be that you have too much error checking for a simple transaction. A failing insert is such an unusual event that you normally wouldn't write code to handle it.
Second, this code won't actually "catch" errors:
IF @ErrorCode <> 0
An error in the SQL statement will abort the stored procedure and return to the client. You'd have to try ... catch to actually handle an error in a stored procedure.
Third, I try to avoid raiserr
. It can do unexpected things both on the server and the client side. Instead, consider using an output
parameter to return error information to the client program.
Related videos on Youtube
![Taryn](https://i.stack.imgur.com/zgBsf.jpg?s=256&g=1)
Taryn
developer, techie, nerd.... ex-DBRE at Stack Overflow, previously a Community Manager for Stack Overflow. When I'm not at my desk, I do CrossFit and run (a lot).
Updated on July 09, 2022Comments
-
Taryn almost 2 years
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.
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
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.
Thanks
-
dburges about 13 yearsWhich version of SQL Server? It makes a big differnce in the method.
-
-
Rich almost 7 yearsGood example! Thanks
-
Andrew over 5 yearsI tried forcing errors at different points using code similar to the above and works perfectly. Thanks!