如何在sql中编写游标来比较两个表数据并从一条记录中找到fifocost

我想写一个游标来比较两个表的数据并找到一个fifo成本。

我已经为游标编写了这个查询，但是它没有得到正确的成本。

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) 
as
Begin
  Declare @qty decimal(18,2),@cost decimal(18,2),@line int
  Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
  Declare MyCur Cursor for 
    select QTY_IN,UNIT_COST,LINE 
    from STOCKDETAILS  
    where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') 
    and PRODUCT_NO=@pid order   by STOCK_DATE
  Declare @fqty decimal(18,2)
  Open MyCur
  Fetch next from mycur into @qty, @cost, @line
  While @@Fetch_Status=0
  Begin
    Declare MyCur1 Cursor for 
       select QTY_IN,UNIT_COST,LINE 
       from STOCKDETAILS  
       where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') 
         and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur1
    Fetch next from mycur1 into @qty1, @cost1, @line1
    While @@Fetch_Status = 0
    Begin
        if @qty>=@qty1 
           set @qty=@qty-@qty1
      set @fqty=@fqty+@qty
      if @qty<@qty1 
        set @qty=@qty-@qty1
      set @fqty=@fqty+@qty-@qty1
      Fetch Next From MyCUr into @qty,@cost,@line
      Fetch Next From MyCUr1 into @qty1,@cost1,@line1
      while @fqty=0
      Begin
        update STOCKDETAILS 
        set FIFO_COST=@cost 
        where LINE=@line1
      End
    End
    Close Mycur
    Close Mycur1
    Deallocate Mycur
    Deallocate Mycur1
  End
End
Exec SP_GETFIFOCOST 'F1'

我在前端写了相同的逻辑，像这样

b = table2.Rows.Count - 1;
try
{
    for (a = table1.Rows.Count - 1; a >= 0; a--)
    {
        total();
        while (fqty != 0 && fqty >= 0)
        {
            if (b >= 0)
            {
                b = b - 1;
                total();
            }
            else
            {
                fqty = 0;
            }
        }
    }
}
catch (Exception EX) { }
void total()
{
    try
    {
        fout = Convert.ToDecimal(table2.Rows[b]["QTY_OUT"].ToString());
    }
    catch (Exception EX) { fout = 0; }
    try
    {
        fin = Convert.ToDecimal(table1.Rows[a]["QTY_IN"].ToString());
    }
    catch (Exception EX) { fin = 0; }
    if (fin >= fout)
    {
        fqty = fin - fout;
        table1.Rows[a]["QTY_IN"] = fqty;
        try
        {
            string fc = table1.Rows[a]["UNIT_COST"].ToString();
            string sn = table2.Rows[b]["LINE"].ToString();
            TRcmd.Connection = cn;
            TRcmd.CommandText = "update STOCKDETAILS set FIFO_COST='" + fc + "' where LINE='" + sn + "'";
            TRcmd.CommandType = CommandType.Text;
            TRcmd.ExecuteNonQuery();
        }
        catch (Exception EX) { }
        if (fqty == 0)
        {
            b = b - 1;
        }
    }
    else
    {
        fqty = 0;
        table1.Rows[a]["QTY_IN"] = fqty;
        try
        {
            string fc = table1.Rows[a]["UNIT_COST"].ToString();
            string sn = table2.Rows[b]["LINE"].ToString();
            TRcmd.Connection = cn;
            TRcmd.CommandText = "update STOCKDETAILS set FIFO_COST='" + fc + "' where LINE='" + sn + "'";
            TRcmd.CommandType = CommandType.Text;
            TRcmd.ExecuteNonQuery();
        }
        catch (Exception EX) { }
        a = a - 1;
        try
        {
            table1.Rows[a]["QTY_IN"] = Convert.ToDecimal(table1.Rows[a]["QTY_IN"].ToString()) + (fin - fout);
        }
        catch (Exception EX) { }
        a = a + 1;
        b = b - 1;
    }
}

你能帮帮我吗?

USE [RISE_DB]
    GO
    /****** Object:  StoredProcedure [dbo].[SP_GETFIFOCOST]    Script Date: 09/15/2015 15:34:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
    Begin
    Declare @qty decimal(18,2),@cost decimal(18,2),@line int
    Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
    Declare @fqty decimal(18,2)
    set @fqty=0
    Declare MyCur1 Cursor for select QTY_OUT,UNIT_COST,LINE from STOCKDETAILS  where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur1
    Declare MyCur Cursor for select QTY_IN,UNIT_COST,LINE from STOCKDETAILS  where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur
    if @fqty=0
    begin
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    Fetch Next From MyCur into @qty,@cost,@line
    End
    While @@Fetch_Status=0
    Begin
    While @@Fetch_Status=0
    Begin
    if @qty>@qty1
    begin
    set @qty=@qty-@qty1
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    set @fqty=@fqty+@qty
    if @fqty>0
    begin
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    set @fqty=@qty-@qty1
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    end
    end
    if @qty<@qty1
    begin
    set @fqty=@qty-@qty1
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    if @fqty<0
    begin
    Fetch Next From MyCur into @qty,@cost,@line
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    set @qty1=@fqty-@qty1
    set @fqty=@qty+@qty1
    if @fqty>0
    begin
    set @qty=@qty+@qty1
    end
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    end
    end
    if @fqty=0
    begin
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    Fetch Next From MyCur into @qty,@cost,@line
    End
    End
    end
    Close MyCur1
    Close MyCur
    Deallocate MyCur1
    Deallocate MyCur
    End