Hi, I am new to SQL can anyone guide me How to work ROLLBACK TRAN. my code is not working and also I need use try-catch block can anyone change it.
ALTER PROCEDURE [dbo].[TransferEquipmentAndProductData_Act2Store]
-- Add the parameters for the stored procedure here
@DocumentGUID varchar(36),
@UserID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--
DECLARE @TransactionName varchar(max)
Set @TransactionName = 'Transaction_TransferEquipmentProductData'
--Begin Transaction
BEGIN TRAN @TransactionName
------------------------------------------------------------
--
--
BEGIN
--
IF EXISTS (Select camosGUID from cC_EquipmentData_Stor with (nolock) where DocumentGUID = @DocumentGUID)
BEGIN
DELETE FROM cC_EquipmentData_Stor WHERE [DocumentGUID] = @DocumentGUID
end
IF EXISTS (SELECT * FROM cC_ProductData_Stor WHERE DocumentGUID = @DocumentGUID)
BEGIN
DELETE FROM cC_ProductData_Stor WHERE DocumentGUID = @DocumentGUID
end
--
insert into cC_EquipmentData_Stor
(camosguid, datenew, usernew, DocumentGUID, clone, oldCamosGUID,
Quantity, ItemLocked, EquipmentLoaded, ItemNo, Consistency, TenderLetterXMLBin, EquipmentBlob)
select camosguid, getdate(), @UserID, @DocumentGUID, clone, oldCamosGUID,
Quantity, ItemLocked, EquipmentLoaded, ItemNo, Consistency, TenderLetterXMLBin, EquipmentBlob
from cC_EquipmentData_Act where DocumentGUID = @DocumentGUID
insert into cC_ProductData_Stor
(camosGUID, DateNew, UserNew, DocumentGUID, EquipmentGUID,SolutionGUID,ProductGUID,ProductName,
SolutionState,NCP,NetworkNumber,NetworkDescription,consistency,ProductPosNo,clone,oldCamosGUID)
select camosGUID, getdate(), @UserID, @DocumentGUID,EquipmentGUID,SolutionGUID,ProductGUID,ProductName,
SolutionState,NCP,NetworkNumber,NetworkDescription,consistency,ProductPosNo,clone,oldCamosGUID
from cC_ProductData_Act where DocumentGUID = @DocumentGUID
--
END
--
IF @@ERROR <> 0
BEGIN
-- Return 0 to the calling program to indicate failure.
ROLLBACK TRAN @TransactionName
Select 0 as ReturnState;
END
ELSE
BEGIN
-- Return 1 to the calling program to indicate success.
COMMIT TRAN @TransactionName
Select 1 as ReturnState;
END
END
What I have tried:
ROLLBACK TRAN
Not working