Cursor Inside try Catch. Data Not Getting Inserted How To Resolve?

See more:

cursor

SQL-Server-2008R2

I have two cursors inside try catch block ,my problem is that th eprocedure is ececuting but the data is not getting inserted inside table,its showing blank. Please refer the Below code and suggest corrections...thanku

USE [Shoper9Ho]
GO
/****** Object:  StoredProcedure [dbo].[SP_HSR_MDM_Mapping11]    Script Date: 16/09/2015 10:01:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Alter PROCEDURE  [dbo].[SP_HSR_MDM_Mapping111]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
   SET NOCOUNT ON;
   SET XACT_ABORT ON;	
   DECLARE	
  @userid1 VARCHAR(50),
  @r VARCHAR(100),
  @User_Code1 VARCHAR(100),
  @Country_Code1 VARCHAR(100),
  @Region_Code1 VARCHAR(100),
  @State_Code1 VARCHAR(100),
  @City_Code1 VARCHAR(100),
  @GLCountry1 VARCHAR(100),
  @GLZone1 VARCHAR(100),
  @GLState1 VARCHAR(100),
  @GLCity1 VARCHAR(100),
  @showroomcode1 varchar(16),
  @shopercompcode varchar(16),
  @CountCity int,
  @countState int,
  @countCountry int,
  @countZone int,
  @trans INT,
  @ErrorNumber INT,
  @ErrorMessage VARCHAR(250),
  @ErrorLine INT

 BEGIN TRY 
	SET @trans = @@TRANCOUNT	
	BEGIN	
		TRANSACTION 
		IF @trans = 0
		BEGIN		
			

					DECLARE Main CURSOR LOCAL FAST_FORWARD
						 FOR select distinct(user_code) from [dbo].[MDMUserMaster]  WITH(NOLOCK) where user_code not in (select Loginid from tshsysdb.[dbo].Vauser);

					OPEN Main; 
					FETCH NEXT FROM Main INTO @userid1;
					WHILE (@@FETCH_STATUS = 0)
					BEGIN 
					 print('Check1' +@userid1);
					 set @r=@userid1 +'123';
					 insert into tshsysdb.[dbo].vauser  values(@userid1,@userid1,'9998',@r); 
					 print('data inserted to vauser');
					 set @shopercompcode=(select CompCode from  [tshsysdb].[dbo].[vacompany] WITH(NOLOCK)) 
				    /**     set @shopercompcode=(select ShoperCompCode from  [dbo].[ShowroomMaster] WITH(NOLOCK) where DistributionCenter=99) **/
					  insert into  tshsysdb.[dbo].vaCompWiseUserPriority values (@shopercompcode,@userid1,'9998','');
                      print('data inserted to vaCompWiseUserPriority');
						set ANSI_NULLS OFF;
						FETCH NEXT FROM Main INTO @userid1;
					END 
					
					--- code to insert the data
					declare mapping_cursor cursor LOCAL FAST_FORWARD for 
					select User_Code,Country_Code,Region_Code,State_Code,City_Code,ShowRoomCode from [dbo].[MDMUserMaster] WITH(NOLOCK) where  imported='N';
					
					open mapping_cursor
					fetch next from mapping_cursor into @User_Code1,@Country_Code1,@Region_Code1,@State_Code1,@City_Code1,@showroomcode1;
					print ('GLCOntry code is '+@Country_Code1);
                    print ('Regin code is '+@Region_Code1);
                    print ('State code is '+@State_Code1);
                    print ('city code is '+@City_Code1);        
                    while (@@fetch_Status=0)           
                    Begin 
					   select @CountCity=count(*) from [dbo].mdmgeolocmapping WITH(NOLOCK)where MDM_GLCountryCode=@Country_Code1 and MDM_GlZoneCode=@Region_Code1 and MDM_GLStateCode=@State_Code1 and  MDM_GLCityCode=@City_Code1; 
					   if(@CountCity>0)
							   BEGIN
								   select  @GLCountry1=MDM_GLCountryName,@GLZone1=MDM_GlZoneName,@GLState1=MDM_GlStateName,@GLCity1=MDM_GLCityName from [dbo].mdmgeolocmapping  WITH(NOLOCK)where MDM_GLCountryCode =@Country_Code1 and MDM_GlZoneCode=@Region_Code1 and MDM_GlStateCode=@State_Code1 and MDM_GLCityCode=@City_Code1;
									IF NOT EXISTS(SELECT 1 FROM [dbo].[RestrictOption] WITH(NOLOCK) WHERE LoginId=@User_Code1 and Country=@GLCountry1 and Zone=@GLZone1 and  State=@GLState1 and   City=@GLCity1 )
									BEGIN
										insert into [dbo].[RestrictOption] Values(@User_Code1,'A',@GLCountry1,@GLZone1,@GLState1,@GLCity1,'04',@showroomcode1);
										 print('data inserted to vaCompWiseUserPriority1'); 
									END
							   END
					   ELSE IF(@COUNTCITY=0)
					   BEGIN
					      select @CountState=count(*) from [dbo].mdmgeolocmapping WITH(NOLOCK)where MDM_GLCountryCode=@Country_Code1 and MDM_GlZoneCode=@Region_Code1 and MDM_GLStateCode=@State_Code1 ; 
					      if(@COUNTSTATE>0)
								  BEGIN
									 select  @GLCountry1=MDM_GLCountryName,@GLZone1=MDM_GlZoneName,@GLState1=MDM_GlStateName from [dbo].mdmgeolocmapping  WITH(NOLOCK)where MDM_GLCountryCode =@Country_Code1 and MDM_GlZoneCode=@Region_Code1 and MDM_GlStateCode=@State_Code1;
									 IF NOT EXISTS(SELECT 1 FROM [dbo].[RestrictOption] WITH(NOLOCK) WHERE LoginId=@User_Code1 and Country=@GLCountry1 and Zone=@GLZone1 and  State=@GLState1)
										BEGIN
											insert into [dbo].[RestrictOption] Values(@User_Code1,'A',@GLCountry1,@GLZone1,@GLState1,null,'04',null); 
											 print('data inserted to vaCompWiseUserPriority12'); 
										END
								  END
						  ELSE IF(@COUNTSTATE=0)
								  BEGIN
									 select @CountZone=count(*) from [dbo].mdmgeolocmapping WITH(NOLOCK)where MDM_GLCountryCode=@Country_Code1 and MDM_GlZoneCode=@Region_Code1 ;
									 IF(@countZone>0)
												 BEGIN
													select  @GLCountry1=MDM_GLCountryName,@GLZone1=MDM_GlZoneName from [dbo].mdmgeolocmapping  WITH(NOLOCK)where MDM_GLCountryCode =@Country_Code1 and MDM_GlZoneCode=@Region_Code1 ;
													IF NOT EXISTS(SELECT 1 FROM [dbo].[RestrictOption] WITH(NOLOCK) WHERE LoginId=@User_Code1 and Country=@GLCountry1 and Zone=@GLZone1)
														   BEGIN
															insert into [dbo].[RestrictOption] Values(@User_Code1,'A',@GLCountry1,@GLZone1,null,null,'04',null); 
															 print('data inserted to vaCompWiseUserPriority123'); 
														   END
												  END
									  Else
												  BEGIN
												  select  @GLCountry1=MDM_GLCountryName from [dbo].mdmgeolocmapping  WITH(NOLOCK)where MDM_GLCountryCode =@Country_Code1;
													IF NOT EXISTS(SELECT 1 FROM [dbo].[RestrictOption] WITH(NOLOCK) WHERE LoginId=@User_Code1 and Country=@GLCountry1 and Zone=@GLZone1)
														   BEGIN
															insert into [dbo].[RestrictOption] Values(@User_Code1,'A',@GLCountry1,null,null,null,'04',null); 
															print('data inserted to vaCompWiseUserPriority1234'+@User_Code1+@GLCountry1); 
														   END
												  END

								  END


					   END
					END

					fetch next from mapping_cursor into @User_Code1,@Country_Code1,@Region_Code1,@State_Code1,@City_Code1,@showroomcode1; 
					
		END	
		BEGIN 
			IF @@TRANCOUNT > 0	--IF @trans = 0	--IF @@ERROR <> 0
			COMMIT TRANSACTION
		END
	
	END TRY 
	BEGIN CATCH		
		SELECT @ErrorNumber= ERROR_NUMBER()
		SELECT @ErrorMessage= ERROR_MESSAGE()
		SELECT @ErrorLine= ERROR_LINE()			
		IF @@TRANCOUNT > 0--IF XACT_STATE() <> 0 AND @trans = 0--IF @@ERROR <> 0 --IF @@TRANCOUNT > 0
		print(@@TRANCOUNT);
		BEGIN 						
			ROLLBACK TRANSACTION
			--Print 'HSR_MDM_Log Inserted start'
			print('ROLL BACK');
		END			
	END CATCH	
	close  mapping_cursor;
	DEALLOCATE mapping_cursor; 
	CLOSE Main;
	DEALLOCATE Main; 
END