How to rollback transaction in new query window

Question

Hi i have one sp where i used try catch with transaction. below is the code

USE [Securtimeweb2.1]
GO
/****** Object:  StoredProcedure [dbo].[UpdateCompany]    Script Date: 7/6/2015 12:14:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UpdateCompany]
(
 
 @CompanyCode varchar(max),
 @NewCompCode varchar(max)=null,
 @TRANSVAL VARCHAR(MAX)
)
as 
BEGIN
 BEGIN TRY
 
 BEGIN TRAN @TRANSVAL
  update BranchMaster set CompanyCode=@NewCompCode Where CompanyCode=@CompanyCode
  COMMIT TRAN @TRANSVAL
  END TRY
  BEGIN CATCH
   SELECT ERROR_MESSAGE() AS strMessage
         ROLLBACK TRAN @TRANSVAL
  END CATCH
 
 
END

then i use this code to run this sp in another query window

EXEC UpdateCompany '002','003','TRAN1'

so after using this in one another query window i am trying to rollback the transaction which is

DECLARE @TRAN VARCHAR(MAX)='TRAN1'
BEGIN TRAN @TRAN
ROLLBACK TRAN @TRAN

so here we can see i given some name for the transaction and trying to rollback with the same name but it's not getting rollback. Am i doing anything wrong here??

score 0 · Accepted Answer · answered Jul 06 '15 at 07:21

0

Use This Links To Know How Transaction Works

http://www.sqlservercurry.com/2011/01/rollback-transaction-in-sql-server.html

answered Jul 06 '15 at 07:21

Laxman Parmar

120
9

rollback transaction i know but wanted to know that if a user has done one transaction from application so i want that there will be undo button show for some second once he again click on undo that transaction should roll back. what should do for that?? – Vikash Jul 06 '15 at 08:57
For that you need to pass two variable if transaction =true then pass that one variable at application side check it from code behind and display there ButtonVisible= true if transaction not done then pass second variable then same like first check from code behind side this time you need to display button visible false. and if user click on that button then update query because You cannot roll back a transaction once it has committed. visit this link http://stackoverflow.com/questions/14016278/is-there-any-way-to-rollback-after-commit-in-mysql – Laxman Parmar Jul 06 '15 at 14:03
Thanks Laxman for your advice i made some idea based upon your suggestion will use it. i am marking it as answer. – Vikash Jul 07 '15 at 05:24

score 0 · Answer 2 · answered Jul 06 '15 at 07:29

I use following standard, maybe it can help :

BEGIN TRAN
BEGIN TRY
    --YOUR SQL STATEMENTS 
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS [STATUS]
    ROLLBACK TRAN
END CATCH
IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRAN
    SELECT 'SUCCESS' AS [STATUS]
END

How to rollback transaction in new query window

2 Answers2