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