ritacse
2/25/2016 - 10:15 AM

SQL loop

SQL loop


 ---------------------------BEGINNING CURSOR DEFINE -------------------------

DECLARE 	@RollID		INT,	  
        @SplitQty	decimal(18,2),	    
        @SplitNote varchar(200),  
  			@Is_Usable  BIT,	  
  			@Has_New_BatchName	BIT,			
  			@Split_BatchCode VARCHAR(50),
  			@CreatorID			BIGINT


		DECLARE @InsertUpdateRollSplit CURSOR
		SET @InsertUpdateRollSplit = CURSOR FOR

		SELECT * FROM @xml_table as xmlTable

		OPEN @InsertUpdateRollSplit
		FETCH NEXT FROM @InsertUpdateRollSplit INTO 
		@RollID, @SplitQty, @SplitNote, @Is_Usable,	@Has_New_BatchName,	@Split_BatchCode, @CreatorID, @CreationDate, @ModifierID, @ModificationDate
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
--------------------- insert Roll Child TABLE ------------------------
	
		INSERT INTO [dbo].[Textile_Fin_Fabric_Roll_Child] 
			([Parent_ID],
			[Roll_No],
			[Fab_Code],
			[F_DIA],
			[F_GSM],
			[Qty],
			[Note],
			[Creator],
			[Creation_Date],
			

		 SELECT rollParent.ID,
            rollChild.Roll_No+'-S'+CAST(isnull(rollChild.New_BatchVersion,0) + 1 AS VARCHAR(10)),
				    rollChild.Fab_Code,
             rollChild.F_DIA,
             rollChild.F_GSM,
             dtXML.Split_Qty,
             dtXML.Split_Note,
             @Creator,
             GETDATE()
                          
						   
      FROM @xml_table dtXML
           INNER JOIN Textile_Fin_Fabric_Roll_Child rollChild ON dtXML.Roll_Id = rollChild.ID
           INNER JOIN Textile_Fin_Fabric_Roll_Parent rollParent ON rollParent.id = rollChild.Parent_ID
			
			WHERE rollChild.ID = @RollID

			DECLARE @NewRollID INT
			SELECT @NewRollID = SCOPE_IDENTITY()
	----------------------END Insert Roll Child TABLE ---------------------
					
				FETCH NEXT
			FROM @InsertUpdateRollSplit INTO 
			@RollID, @SplitQty, @SplitNote, @Is_Usable,	@Has_New_BatchName,	@Split_BatchCode, @CreatorID, @CreationDate, @ModifierID, @ModificationDate
			END

			CLOSE @InsertUpdateRollSplit
			DEALLOCATE @InsertUpdateRollSplit
			 
	------------------------ END OF CURSOR DEFINE ------------------------
			
----SQL While loop

DECLARE @VarDate Datetime = '2-17-2016'

WHILE @VarDate <= '2-22-2016'
BEGIN
         SELECT dbo.GetWeekDayByDateTime(@VarDate)  -- Function
         
         --  exec dbo.HRMS_Attendance_Register_T_Insert_SP @VarDate   --SP
         
  SET @VarDate = DATEADD(DAY, 1, @VarDate)
END 

----SQL loop with inner join for delete

Delete [dbo].[EmployeeWiseKPIChild] from  [dbo].[EmployeeWiseKPIChild] c
			inner join @xml_table  xmlT on c.id = xmlT.Id
			where c.Id = xmlT.Id					
			SET @valid_Msg='Delete Successfully'
			
	----SQL While loop for int value
	DECLARE @CompId int = 1
WHILE @CompId <= 32
BEGIN
         
INSERT INTO [dbo].[Department]    ([Name] ,[CompanyID])	VALUES     ('Operation' ,@CompId)
INSERT INTO [dbo].[Department]    ([Name] ,[CompanyID])	VALUES     ('Utility' ,@CompId)
         
  SET @CompId = @CompId+1
END 
 
Create PROCEDURE [dbo].[KPI_Wise_Target_Setup_T_X_Bulk_Insert_SP]
		@lstOfParentData ntext,		
		@lstOfChildData ntext,		
		@User   bigint
 
 AS
 BEGIN
 BEGIN TRY
 BEGIN TRANSACTION

 DECLARE @TargetIDOut varchar(20) 
 DECLARE @OutParentData int
 DECLARE @OutChildData int
 DECLARE @Code as Varchar(20)

 EXEC sp_xml_preparedocument @OutParentData OUTPUT, @lstOfParentData

  DECLARE @TempParentT TABLE
				(	
				[TargetEmpID] [bigint] NOT NULL,
				[TargetFromYear] [int] NOT NULL,
				[TargetFromMonth] [int] NOT NULL,
				[TargetToYear] [int] NOT NULL,
				[TargetToMonth] [int] NOT NULL,
				[TargetRemarks] [varchar](250) NULL)

		INSERT INTO @TempParentT
			([TargetEmpID] , [TargetFromYear] , [TargetFromMonth] , [TargetToYear] , [TargetToMonth] , [TargetRemarks])
	   
			SELECT [TargetEmpID] , [TargetFromYear] , [TargetFromMonth] , [TargetToYear] , [TargetToMonth] , [TargetRemarks]
	   
	   FROM Openxml( @OutParentData,'/ArrayOfE_KPI_Wise_Target_Setup_Parent_T/E_KPI_Wise_Target_Setup_Parent_T', 3) 
			WITH 
			( [TargetEmpID] [bigint] ,
				[TargetFromYear] [int] ,
				[TargetFromMonth] [int] ,
				[TargetToYear] [int] ,
				[TargetToMonth] [int] ,
				[TargetRemarks] [varchar](250) 
			)	


Declare @KPITargetIdNew as int
DECLARE @parentEmp int
DECLARE db_cursor CURSOR FOR 

SELECT [TargetEmpID] --// uniq in table 
FROM @TempParentT

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @parentEmp

WHILE @@FETCH_STATUS = 0  
BEGIN  
      Set @KPITargetIdNew =Isnull(( Select Top(1) [TargetID]  from [KPI_Wise_Target_Setup_Parent_T_X] group by [TargetID] order by [TargetID] desc),0)+1
 
 INSERT INTO [KPI_Wise_Target_Setup_Parent_T_X]
			([TargetID],[TargetEmpID] , [TargetFromYear] , [TargetFromMonth] , [TargetToYear] , [TargetToMonth] , [TargetRemarks] ,[TargetApprovalCode], [TargetVersion] , [TargetCreator] , [TargetCreationDate] )
		
		SELECT @KPITargetIdNew,[TargetEmpID] , [TargetFromYear] , [TargetFromMonth] , [TargetToYear] , [TargetToMonth] , [TargetRemarks], NULL, 0, @User, GETDATE()
	   FROM @TempParentT WHERE [TargetEmpID]=@parentEmp
	   
	   
	   SET @Code = dbo.HRMS_Custom_Code_Creator ('TRG',@KPITargetIdNew,10,'')
		UPDATE [KPI_Wise_Target_Setup_Parent_T_X] SET TargetCode= @Code 
		WHERE  [TargetID] = @KPITargetIdNew 

      FETCH NEXT FROM db_cursor INTO @parentEmp 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

			Select @KPITargetIdNew as ID, @Code as Code, 0 as Version, 'True' as IsSuccess, 'Data Save Success' as Message

COMMIT TRANSACTION

 END TRY
 BEGIN CATCH
 ROLLBACK TRANSACTION

	DECLARE @ErrorNumber_INT INT;
	DECLARE @ErrorSeverity_INT INT;
	DECLARE @ErrorProcedure_VC VARCHAR(200);
	DECLARE @ErrorLine_INT INT;
	DECLARE @ErrorMessage_NVC NVARCHAR(4000);
	SELECT
	@ErrorMessage_NVC = ERROR_MESSAGE(),
	@ErrorSeverity_INT = ERROR_SEVERITY(),
	@ErrorNumber_INT = ERROR_NUMBER(),
	@ErrorProcedure_VC = ERROR_PROCEDURE(),
	@ErrorLine_INT = ERROR_LINE()
	EXEC [HRMS_Error_Log_T_Write_SP] @ErrorNumber_INT,@ErrorSeverity_INT,@ErrorProcedure_VC,@ErrorLine_INT,@ErrorMessage_NVC,@TargetIDOut 
 RETURN
 END CATCH

 SELECT @TargetIDOut

 END
DECLARE @TempID int, 
        @SL int=1
DECLARE db_cursor CURSOR FOR 
 
SELECT [MapID] --// uniq in table 
FROM [dbo].[Size_Serial_Map_T] where GroupCode='255' order by[SerialNo]

OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @TempID
 
WHILE @@FETCH_STATUS = 0  
BEGIN      
 	--SELECT @SL,  [SerialNo] FROM [dbo].[Size_Serial_Map_T] where GroupCode='255' and [SerialNo]=@TempID
	
	UPDATE [dbo].[Size_Serial_Map_T] 
	set [SerialNo]=@SL
	where GroupCode='255' and [MapID]=@TempID
	
	SET @SL=@SL+1
	
	IF @SL >143
		BEGIN
			BREAK	 
		END 
	   
      FETCH NEXT FROM db_cursor INTO @TempID 
END 
 
CLOSE db_cursor  
DEALLOCATE db_cursor 
DECLARE @document_type int ,
@document_code varchar(20),
@version int

DECLARE db_cursor CURSOR FOR 
 
SELECT [document_type],	[document_code] ,	[version] 	 --// uniq in table 
FROM #Doc_T
 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @document_type, @document_code, @version
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
  
  --- DO your code here: Insert/Update/ Delete/ any query

      FETCH NEXT FROM db_cursor INTO  @document_type, @document_code, @version
END 
 
CLOSE db_cursor  
DEALLOCATE db_cursor