locked
how to write two cursor inside each other RRS feed

  • Question

  • User-1634604574 posted

    i have two cursor i want first row from cursor 1 is inserted then insert first from cursor2 and so on i wrote this but is not working

    alter proc INSERT_Work_Order_from_production_plan  --'PL-10'
    @series varchar(50)
    as begin
    
    DECLARE 
     @status varchar(50)=null, 
     @production_item_ID varchar(50)=null,
     @bom_no_ID varchar(50)=null,
     @company_ID varchar(50)=null,
     @qty varchar(50)=null,
     @planned_start_date varchar(50)=null,
     @description varchar(50)=null,
     @stock_uom_ID varchar(50)=null,
     @production_plan_ID varchar(50)=null,
     @sales_order_ID varchar(50)=null,
     @fg_warehouse_ID varchar(50)=null,
     @FK_series varchar(50)=null,
    @material_request_ID varchar(50)=null;
    
    
    DECLARE 
    
    @item_code_ID varchar(50),
    @source_warehouse_ID varchar(50),
    @item_name varchar(50),
    @description2 varchar(50),
    @required_qty varchar(50),
    @transferred_qty varchar(50),
    @allow_alternative_item bit,
    @include_item_in_manufacturing bit,
    @consumed_qty varchar(50),
    @available_qty_at_source_warehouse varchar(50),
    @available_qty_at_wip_warehouse varchar(50),
    @FK_series2 varchar(50)
    
    set @transferred_qty=0
    set @allow_alternative_item=0
    set @include_item_in_manufacturing=0
    set @consumed_qty=0
    set @available_qty_at_wip_warehouse=0
    
    
    iF(NULLIF(@FK_series2,'') is null)
    begin
    set @FK_series2 = (select top 1 series from Work_Order order by ID desc)
    end
    
    
     set @status='Draft'
     set @planned_start_date=(SELECT CONVERT(date, getdate()))
    
    DECLARE db_cursor CURSOR FOR  
    SELECT 
    Production_Plan_Item.bom_no_D,
    Production_Plan_Item.planned_qty,
    Production_Plan_Item.sales_order_ID,
    Warehouse.warehouse_name,
    item.item_name,
    item.description,
    UOM.uom,
    Production_Plan_Item.FK_series,
    company.company_name,
    Production_Plan_Item.material_request_ID
    
     from Production_Plan_Item  join item on item.series=Production_Plan_Item.item_code_ID join Production_Plan on Production_Plan.series=Production_Plan_Item.FK_series
     join uom on uom.series=Production_Plan_Item.stock_uom_ID
     join Warehouse on Warehouse.series=Production_Plan_Item.warehouse_ID
     join company on company.series=Production_Plan.company_ID 
    
       where Production_Plan_Item.FK_series=@series and  Production_Plan.status not in('Draft','Cancelled')
    
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @bom_no_ID,@qty ,@sales_order_ID,@fg_warehouse_ID,@production_item_ID,@description,@stock_uom_ID,@FK_series,@company_ID,@material_request_ID
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        
    exec INSERT_Work_Order
    	
    @bom_no_ID=@bom_no_ID,
    @qty=@qty,
    @sales_order_ID=@sales_order_ID,
    @fg_warehouse_ID=@fg_warehouse_ID,
    @production_item_ID=@production_item_ID,
    @description=@description,
    @stock_uom_ID=@stock_uom_ID,
    @production_plan_ID=@production_plan_ID,
    @company_ID=@company_ID,
    @planned_start_date=@planned_start_date,
    @status=@status,
    @material_request_ID=@material_request_ID
    
    
    
    
    --insert work order item
    
    DECLARE db_cursor2 CURSOR FOR  
    SELECT 
    Material_Request_Plan_Item.actual_qty,
    Material_Request_Plan_Item.description,
    item.item_code,
    Material_Request_Plan_Item.item_name,
    Material_Request_Plan_Item.quantity,
    Warehouse.warehouse_name
    
    
     from Material_Request_Plan_Item  
     join item on item.series=Material_Request_Plan_Item.item_code_ID 
     join Production_Plan on Production_Plan.series=Material_Request_Plan_Item.FK_series
     join Warehouse on Warehouse.series=Material_Request_Plan_Item.warehouse_ID
    
    
       where Material_Request_Plan_Item.FK_series=@series
    
    OPEN db_cursor2   
    FETCH NEXT FROM db_cursor2 INTO  @available_qty_at_source_warehouse,@description,@item_code_ID,@item_name,@required_qty,@source_warehouse_ID
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        
    exec INSERT_Work_order_Item
    	
    
    @item_code_ID=@item_code_ID,
    @source_warehouse_ID=@source_warehouse_ID,
    @item_name=@item_name,
    @description=@description2,
    @required_qty=@required_qty,
    @transferred_qty=@transferred_qty,
    @allow_alternative_item=@allow_alternative_item,
    @include_item_in_manufacturing=@include_item_in_manufacturing,
    @consumed_qty=@consumed_qty,
    @available_qty_at_source_warehouse=@available_qty_at_source_warehouse,
    @available_qty_at_wip_warehouse=@available_qty_at_wip_warehouse,
    @FK_series=@FK_series2
    
    
        -- fetch next row from cursor
        FETCH NEXT FROM db_cursor2 INTO   @available_qty_at_source_warehouse,@description,@item_code_ID,@item_name,@required_qty,@source_warehouse_ID
    END   
    
    CLOSE db_cursor2   
    DEALLOCATE db_cursor2
    
    
    
    FETCH NEXT FROM db_cursor INTO  @bom_no_ID,@qty ,@sales_order_ID,@fg_warehouse_ID,@production_item_ID,@description,@stock_uom_ID,@FK_series,@company_ID,@material_request_ID
    -----------------------------------------------------------------------------------------------------
    
    
    END   
    
    CLOSE db_cursor   
    DEALLOCATE db_cursor
    
    
    end

    Thursday, November 28, 2019 10:32 AM

All replies

  • User753101303 posted

    Hi,

    this is not working

    Please always tell what happens. Else we have to read or test the code and it's not uncommon to see multiple possible issues (or not the same then  the one you have). The outer loop ends too soon ? (given what you told, it could be a compile time or a runtime error)

    For now I suspect a problem as @@FETCH_STATUS is upated by the inner loop and tested on the next outer loop iteration. I would likely save this value in a variable...

    Thursday, November 28, 2019 11:05 AM
  • User-1634604574 posted

    i have two table t1 and t2 i want insert data into these two tables by cursor at the same time it mean at first when first row is inserted into t1 then insert data into t2

    and so on

    Thursday, November 28, 2019 7:39 PM
  • User288213138 posted

    Hi zhyanadil,

    i have two table t1 and t2 i want insert data into these two tables by cursor at the same time it mean at first when first row is inserted into t1 then insert data into t2

    You don't need 2 cursors, you just need to insert data separately.

    The code:

    DECLARE db_cursor1 CURSOR FOR
    SELECT Id,Name FROM TableC
    
    Declare @Id int,@Name nchar(10);
    
    open db_cursor1
    fetch next from db_cursor1 into
    @Id,@Name
    
    while @@FETCH_STATUS = 0  
    BEGIN
    
    INSERT INTO TableA(IdA, NameA) VALUES (@Id,@Name)
    fetch next from db_cursor1 into
    @Id,@Name
    
    INSERT INTO TableB(IdB, NameB) VALUES (@Id,@Name)
    fetch next from db_cursor1 into
    @Id,@Name
    
    END
    close db_cursor1
    deallocate db_cursor1
    
    
    select * from TableA
    select * from TableB

    My table:

    CREATE TABLE [dbo].[TableA]
    (
    	[IdA] INT NULL , 
        [NameA] NCHAR(10) NULL
    )
    
    CREATE TABLE [dbo].[TableB]
    (
    	[IdB] INT NULL , 
        [NameB] NCHAR(10) NULL
    )
    CREATE TABLE [dbo].[TableC]
    (
    	[Id] INT NULL, 
        [Name] NCHAR(10) NULL 
    )
    

    The result:

    Best regards,

    sam

    Friday, November 29, 2019 10:03 AM
  • User364663285 posted

    It is suggested not to complicate the case, to mix cursors together.

    You can also use temp table to achieve the same like cursor is doing for you.

    Saturday, November 30, 2019 3:15 PM