Multiple transaction in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple transaction in stored procedure

Author

Topic

Sumathy
Starting Member

2 Posts

Posted - 2014-10-15 : 07:44:23

Hi,
Below is my code.I want to use 3 transactions in a single stored procedure.First it should update the value and then if the 1st transaction executed successfully then 2nd transaction should start,if 2nd trans executed successfully then 3rd trans should execute.After that only i want to commit all the transactions that is 1,2,3.If any of the trans fails the other shouldn't execute.How do i do this?


USE [recruit]
GO
/****** Object:  StoredProcedure [dbo].[Import]    Script Date: 10/15/2014 17:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Import] 
@CustId int
AS
BEGIN TRY
BEGIN TRAN

UPDATE EMImportListing set FlgEmlisting='N' where FlgEmlisting IS NULL

 INSERT INTO EMListing (CustId,ImportId,StartDate,EndDate,DTRun,IsDraft,IsFeatured,IsApproved,
             Title, Description, Catid, Address1, Address2, City, State, PostalCode, Country, 
             Phone,Email,URL,JobCity,JobState,JobPostalCode,JobCountry,
            FirstName,LastName,OrgName,HideContactInfo,HideOrgName,HideName,HidePhone,HideEmail, 
             PlanOrSubID,PlanSource,MaxPhotos,IncludesURL,IncludesVideo,IncludesFeatured,ImportReferenceNumber
             ) 
              SELECT 
             CustId,ImportId,StartDate,EndDate,DTRun,IsDraft,IsFeatured,IsApproved,
             Title, Description, Catid, Address1, Address2, City, State, PostalCode, Country, 
            Phone,Email,URL,JobCity,JobState,JobPostalCode,JobCountry,
             FirstName,LastName,OrgName,HideContactInfo,HideOrgName,HideName,HidePhone,HideEmail, 
             PlanOrSubID,PlanSource,MaxPhotos,IncludesURL,IncludesVideo,IncludesFeatured,ImportReferenceNumber
             FROM EMImportListing WHERE EMImportListing.CustId=@CustId and FlgEmlisting='N' 

UPDATE EMImportListing set FlgEmlisting='Y' where FlgEmlisting='N'

 COMMIT TRAN
 END TRY
  

 BEGIN CATCH
 SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState = ERROR_STATE(),
ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage = ERROR_MESSAGE()
 END CATCH

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-15 : 08:34:18

What you described is what the code that you have currently would do, except, I would set XACT_ABORT to ON. So, insert a

SET XACT_ABORT  ON

along with the other SET statements you have.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 08:34:57

Not sure why you want such a complicated set up. How about:

begin try
begin tran
insert ... where FlgEmlisting IS NULL
if @@rowcount > 0
UPDATE EMImportListing set FlgEmlisting='Y' where FlgEmlisting='N'
commit
end try

begin catch
select ... error info
rollback
end catch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources