locked
ROLLBACK TRANSACTION in a stored procedure RRS feed

  • Question

  • Hi,

    I have created a procedure. I want to apply ROLLBACK TRANSACTION when STEP 1 is not performed to restrict  STEP 2.

    Need inputs.

    USE [Dev_Employee_Management] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_PurgeData] AS BEGIN SET XACT_ABORT, NOCOUNT ON DECLARE @starttrancount INT Begin TRY SELECT @starttrancount = @@TRANCOUNT IF @starttrancount = 0 BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on -----------**** STEP 1 ****-------------- INSERT INTO [dbo].[Employee_History] SELECT * FROM [dbo].[Employee] WHERE startdate IN ( SELECT DISTINCT startdate FROM [dbo].[Employee] WHERE startdate < DATEADD(day,-3, GETDATE()) ) -----------**** STEP 2 ****---------------- DELETE FROM [dbo].[Employee] WHERE startdate IN ( SELECT DISTINCT startdate FROM [dbo].[Employee] WHERE startdate < DATEADD(day,-3, GETDATE()) )

    DELETE FROM [dbo].[Employee_History] WHERE startdate IN ( SELECT DISTINCT startdate FROM [dbo].[Employee_History] WHERE startdate <= DATEADD(day,-5, GETDATE()) ) IF @starttrancount = 0 COMMIT TRANSACTION SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off RETURN 0 End Try Begin Catch -- Test if the transaction is uncommittable. IF XACT_STATE() <> 0 AND @starttrancount = 0 BEGIN ROLLBACK TRANSACTION; END; -- when a run time error happens the values stored into error table Insert into dbo.Error(vErrNumber,vErrSeverity,vErrState,vErrProcedure,vErrLine,vErrMessage,vErrType,vMessage,vUser) SELECT ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(),'System','Stored Procedure Error',SYSTEM_USER End Catch END GO

    Thanks.


    Vamshi Janagama

    Wednesday, October 9, 2019 5:01 AM

Answers

  • I have created a procedure. I want to apply ROLLBACK TRANSACTION when STEP 1 is not performed to restrict  STEP 2.

    Need inputs.

    See this article on my web site for an introduction to error and transaction handling in SQL Server:
    http://www.sommarskog.se/error_handling/Part1.html

              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

    No, don't do that. This can lead to nasty errors where some rows are only deleted from the source table but not inserted into the history table. Or vice versa.

    As a matter of fact that could happen also with the default isolation level of READ COMMITTED (but not with the same risk), so the best would be to get the IDs for the rows to archive so that you know that you insert and delete exactly the same rows. But even then READ UNCOMMITTED is entirely impermissible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 9, 2019 9:52 PM

All replies

  • I want to apply ROLLBACK TRANSACTION when STEP 1 is not performed

    What do you mean by this, can you explain it more detailed, please?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 9, 2019 6:04 AM
  • Hi Olaf Helper

    I need to purge the data.

    In step 1:    IF data exists in the table [dbo].[Employee], I am trying to fetch the data from [dbo].[Employee] and then insert the same data into [dbo].[Employee_History]. If the data is successfully inserted to [dbo].[Employee_History] with out any errors etc. then only i want to execute Step 2 conditions to delete the data from [dbo].[Employee] in Step 2    otherwise i dont want to delete the data from [dbo].[Employee].

    please advise.

    Thanks.


    Vamshi Janagama



    • Edited by Vamshi K J Wednesday, October 9, 2019 3:42 PM
    Wednesday, October 9, 2019 3:36 PM
  • I have created a procedure. I want to apply ROLLBACK TRANSACTION when STEP 1 is not performed to restrict  STEP 2.

    Need inputs.

    See this article on my web site for an introduction to error and transaction handling in SQL Server:
    http://www.sommarskog.se/error_handling/Part1.html

              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

    No, don't do that. This can lead to nasty errors where some rows are only deleted from the source table but not inserted into the history table. Or vice versa.

    As a matter of fact that could happen also with the default isolation level of READ COMMITTED (but not with the same risk), so the best would be to get the IDs for the rows to archive so that you know that you insert and delete exactly the same rows. But even then READ UNCOMMITTED is entirely impermissible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 9, 2019 9:52 PM