Here is codeLast statement is insert into temp values('sss').temp table does not exists.Why XACT_ABORT is not rolling back all data manipulation in transaction?CREATE PROCEDURE dbo.usp_UI_MoveAccount( @AccountID int, @SourceDeptID int, @DestDeptID int)AS SET NOCOUNT ONSET XACT_ABORT ONset transaction isolation level read uncommittedBEGIN TRANDECLARE @tblAccount_KeywordCreative TABLE( Ident int IDENTITY (1, 1) NOT NULL, KeywordCreativeID int, SortIndex int)INSERT INTO @tblAccount_KeywordCreative SELECT KeywordCreativeID,SortIndexFROM tblAccount_KeywordCreative AKCWHERE AKC.AccountID=@AccountIDprint '@tblAccount_KeywordCreative created'DECLARE @tblListing TABLE( ListingID int, AdGroupID int)INSERT INTO @tblListing SELECT ListingID,AdGroupIDFROM tblListingWHERE AccountID=@AccountIDprint '@tblListing created'DECLARE @tblAdGroupCreative TABLE( AdGroupCreativeID int, KeywordCreativeID int )IF EXISTS (Select * from @tblListing WHERE AdGroupID IS NOT NULL)BEGIN INSERT INTO @tblAdGroupCreative Select AGC.AdGroupCreativeID,AGC.KeywordCreativeID FROM @tblListing l inner join tblAdGroup_AdGroupCreative A_AC on A_AC.AdGroupID=l.AdGroupID inner join tblAdGroupCreative AGC on AGC.AdGroupCreativeID=A_AC.AdGroupCreativeID WHERE l.AdGroupID IS NOT NULLENDprint '@tblAdGroupCreative created'DECLARE @tblListingCreative TABLE( ListingCreativeID int, KeywordCreativeID int )INSERT INTO @tblListingCreative Select LC.ListingCreativeID,LC.KeywordCreativeIDFROM @tblListing l inner join tblListing_ListingCreative L_LC on L_LC.ListingID=l.ListingIDinner join tblListingCreative LC on LC.ListingCreativeID=L_LC.ListingCreativeIDprint '@tblListingCreative created'DELETE FROM tblAccount_KeywordCreative WHERE AccountID=@AccountIDprint '@tblListingCreative deleted'DECLARE @curr_id int,@highest_id int,@next_id intDECLARE @NewKeywordCreativeID int,@NewKeywordTemplateGroupID int,@CurrKeywordCreativeID intSELECT @curr_id=0SELECT @highest_id=MAX(Ident) FROM @tblAccount_KeywordCreative WHILE @curr_id <@highest_idBEGIN SELECT TOP 1 @next_id=Ident FROM @tblAccount_KeywordCreative WHERE Ident>@curr_id ORDER BY Identprint @next_id SELECT @NewKeywordCreativeID=dbo.ufn_CkeckKeywordCreativeExists(@DestDeptID, ProductTypeCode, LanguageCode, PRDTitle,PRDDescription, PRDDescription1, PRDDisplayUrl, CTYTitle, CTYDescription, CTYDescription1, CTYDisplayUrl, CNTTitle, CNTDescription, CNTDescription1,CNTDisplayUrl, GENTitle, GENDescription, GENDescription1, GENDisplayUrl) FROM @tblAccount_KeywordCreative t Inner Join tblKeywordCreative KC ON KC.KeywordCreativeID=t.KeywordCreativeID WHERE t.Ident=@next_id IF @NewKeywordCreativeID=0 BEGIN INSERT INTO tblKeywordCreative SELECT @DestDeptID, ProductTypeCode, LanguageCode, KeywordCreativeName, PRDTitle, PRDDescription, PRDDescription1, PRDDisplayUrl, CTYTitle, CTYDescription, CTYDescription1, CTYDisplayUrl, CNTTitle, CNTDescription, CNTDescription1, CNTDisplayUrl, GENTitle, GENDescription, GENDescription1, GENDisplayUrl, InsertDate, UpdateDate, UpdatedByUserID, TempDataImport_NonGoogleKeywordDetailID, KC.TempDataImport_GoogleCountryID FROM @tblAccount_KeywordCreative t Inner Join tblKeywordCreative KC ON KC.KeywordCreativeID=t.KeywordCreativeID WHERE t.Ident=@next_id SELECT @NewKeywordCreativeID=SCOPE_IDENTITY() END IF NOT EXISTS (SELECT * FROM tblAccount_KeywordCreative WHERE AccountID=@AccountID and KeywordCreativeID=@NewKeywordCreativeID) BEGIN INSERT INTO tblAccount_KeywordCreative SELECT @AccountID,@NewKeywordCreativeID,SortIndex FROM @tblAccount_KeywordCreative t WHERE t.Ident=@next_id END SELECT @CurrKeywordCreativeID=KeywordCreativeID FROM @tblAccount_KeywordCreative t WHERE t.Ident=@next_id IF EXISTS (Select * from @tblAdGroupCreative) BEGIN UPDATE tblAdGroupCreative SET KeywordCreativeID=@NewKeywordCreativeID WHERE AdGroupCreativeID IN (select AdGroupCreativeID FROM @tblAdGroupCreative WHERE KeywordCreativeID=@CurrKeywordCreativeID ) END IF EXISTS (Select * from @tblListingCreative) BEGIN UPDATE tblListingCreative SET KeywordCreativeID=@NewKeywordCreativeID WHERE ListingCreativeID IN (select ListingCreativeID FROM @tblListingCreative WHERE KeywordCreativeID= @CurrKeywordCreativeID) END SET @curr_id=@next_idEND DECLARE @S_KeywordTemplateGroupID intSELECT @S_KeywordTemplateGroupID=KeywordTemplateGroupID FROM tblAccount WHERE AccountID=@AccountID and DepartmentID=@SourceDeptIDIF EXISTS (Select * from tblKeywordTemplateGroup_KeywordTemplate WHERE KeywordTemplateGroupID=@S_KeywordTemplateGroupID )BEGIN SELECT @NewKeywordTemplateGroupID=dbo.ufn_CheckKeywordTemplateGroupExists(@S_KeywordTemplateGroupID,@DestDeptID) IF @NewKeywordTemplateGroupID=0 BEGIN INSERT INTO tblKeywordTemplateGroup SELECT @DestDeptID,KTG.KeywordTemplateGroupName, KTG.InsertDate,KTG.UpdateDate,KTG.UpdatedByUserID, KTG.TempDataImport_OldTemplateGroupID FROM tblKeywordTemplateGroup KTG WHERE KTG.KeywordTemplateGroupID=@S_KeywordTemplateGroupID SELECT @NewKeywordTemplateGroupID=SCOPE_IDENTITY() INSERT INTO tblKeywordTemplateGroup_KeywordTemplate SELECT @NewKeywordTemplateGroupID,KTGKT.KeywordTemplateID, KTGKT.InsertDate,KTGKT.InsertedByUserID FROM tblKeywordTemplateGroup_KeywordTemplate KTGKT WHERE KeywordTemplateGroupID=@S_KeywordTemplateGroupID END UPDATE tblAccount SET DepartmentID=@DestDeptID,KeywordTemplateGroupID=@NewKeywordTemplateGroupID WHERE AccountID=@AccountID and DepartmentID=@SourceDeptIDENDELSEBEGIN UPDATE tblAccount SET DepartmentID=@DestDeptID WHERE AccountID=@AccountID and DepartmentID=@SourceDeptIDENDinsert into temp values('sss')COMMIT TRANGO
mk_garg