Click here to Skip to main content
15,888,579 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 13-Apr-18 6:08am
Comments
CHill60 19-Mar-18 7:00am    
What do you mean by "Not working". Is there an error message or is it just that the data is persisted on the database
#realJSOP 19-Mar-18 7:11am    
You may as well declare the transaction name as 32 characters, because only the first 32 characters are used. Also, I would use a unique identifier instead of a hard-coded string.

declare @transactionName nvarchar(32) = REPLACE(NEWID(),'-','')

It is guaranteed to be unique every time you call the stored proc, thus avoiding possible concurrency issues.
Rajesh Pandya 3-Apr-18 2:59am    
For implement try catch in stored procedure check the below link:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

1 solution

The @@error variable only tells you whether there was an error in the previous SQL statement so right now you're only checking the last INSERT statement for errors, and not the first INSERT or the two DELETES.

see @@error in SQL Docs for more information
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900