Stored Procedure to return value based on Transaction in SQL Server

amar

SOLVED CLOSED ACTIVE ANSWERED
User: amar
Posted: on Oct 09, 2018 03:39 AM
Forum: SQL Server
Answer: 1
Views: 3678

Can anyone help on rollback and commit transaction

If the procedure excute correct need to out value 1 else 0

      
ALTER proc [dbo].[gsp_SaveScripts]                    
(                  
@CampaignScriptTypeID int = NULL,    
@DataElementID int =NULL,    
@Script varchar(max)= NULL,    
@UserID int =NULL,    
@CreateDate datetime = NULL,    
@UpdateDate Datetime = NULL,  
@Opening varchar(max)= NULL,    
@VoiceMail varchar(max)= NULL,    
@QualityAssurance varchar(max)= NULL    
)                  
As                    
                
BEGIN
declare @Out int
BEGIN TRAN      
if Exists(Select * from Scripts(nolock) where CampaignScriptTypeID=@CampaignScriptTypeID and DataElementID=@DataElementID)    
Begin    
    Update Scripts Set  
    Opening=@Opening,VoiceMail=@VoiceMail,QualityAssurance=@QualityAssurance  
    where CampaignScriptTypeID=@CampaignScriptTypeID  
    
     Update Scripts Set Script=@Script,UpdateDate=getdate()   
    where CampaignScriptTypeID=@CampaignScriptTypeID and DataElementID=@DataElementID  
 End    
 else    
 Begin    
  Insert Into Scripts(CampaignScriptTypeID,DataElementID,Script,UserID,CreateDate,UpdateDate,VoiceMail,QualityAssurance,Opening)    
 Values(@CampaignScriptTypeID,@DataElementID,@Script,@UserID,GETDATE(),GETDATE(),@VoiceMail,@QualityAssurance,@Opening)     
 End    
IF @@ERROR <> 0
ROLLBACK TRAN 
ELSE COMMIT TRAN
PRINT @@TRANCOUNT
END

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Oct 09, 2018 05:41 AM
Modified: on Oct 09, 2018 05:42 AM
Report

Hi amar,

Check this test query. Now please take its reference and correct your query.

SQL

CREATE PROCEDURE [dbo].[InsertUpdateCustomer]
(                 
	@ID int = NULL, 
	@Name varchar(max)= NULL, 
	@Country varchar(max)= NULL
)                 
AS               
BEGIN
	DECLARE @Out INT

	BEGIN TRANSACTION
	IF EXISTS(SELECT * FROM Customers(NOLOCK) WHERE CustomerId = @ID AND Country = @Country)
	BEGIN   
		UPDATE Customers 
		SET Name = @Name
		WHERE CustomerId = @ID
	END   
	ELSE   
	BEGIN   
		INSERT INTO Customers (Name,Country) VALUES (@Name,@Country)
	END  

	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION
		SET @Out = 0
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		SET @Out = 1
	END
		
	SELECT @Out 'Status'
END
GO
-- Executed Successfully so Transaction Comitted.
EXEC InsertUpdateCustomer 1,'John Hammond','United States' 
GO
-- Error so Transaction Rolledback.
EXEC InsertUpdateCustomer 2,'John Hammond','United States'

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Stored Procedure to return value based on Transaction in SQL Server

Company

Explore

Follow us

Contact Us