日期:2014-05-19  浏览次数:20422 次

怎样在存储过程中实现按序号遍历记录集
各给兄弟姐妹:
在SQL   SERVER   中想像写类似这样的循环该怎么办?
while(count   >   0)
{
      do   something;
      cout   --;
}
比如:
set   @count   =   (select   count(*)   from   StockBillEntry   where   DocID   =   @DocID)
--   遍历所有分录
while(@count   >   0)
begin
--就是想按序号遍历记录集,可是在下面我写的地方会出现选出的记录不止一条
set   @MateriaID   =   (select   MaterialID   from   StockBillEntry   where   DocID   =   @DocID)
set   @LocID   =   (select   LocID   from   StockBillEntry   where   DocID   =   @DocID)
set   @BachID   =   (select   BatchNum   from   StockBillEntry   where   DocID   =   @DocID)
set   @Duredate   =   (select   StoragePeriod   from   StockBillEntry   where   DocID   =   @DocID)
set   @ProductDate   =   (select   ProductDate   from   StockBillEntry   where   DocID   =   @DocID)
set   @GoodMove   =   (select   LogisticCode   from   StockBillEntry   where   DocID   =   @DocID)
set   @Amount   =   (select   BasicQty   from   StockBillEntry   where   DocID   =   @DocID)
set   @Money   =   (select   PogOC   from   StockBillEntry   where   DocID   =   @DocID)
--   找此记录是否存在,如果存在就更新,否则就插入
declare   @Exist   int  
set   @Exist   =   (select   count(*)   from   IcInventory   where   AccountNO   =   @CompanyID   and   MaterialID   =   @MateriaID   and   StorehouseID   =   @StockHouse   and   LocID   =   @LocID   and   BatchNum   =   @BachID   and   StoragePeriod   =   @Duredate)
if(@Exist   =   0)
--   新增记录
insert   into   IcInventory(AccountNO   ,MaterialID   ,   StorehouseID   ,LocID   ,   BatchNum   ,StoragePeriod,BasicQty,PogOC)   values(@CompanyID,@MateriaID   ,@StockHouse   ,@LocID   ,@BachID,@Duredate,@Amount,@Money)
else  
begin
declare   @Basic   decimal
declare   @Cost   decimal
set   @Cost   =   (select   PogOC     from   IcInventory   where   AccountNO   =   @CompanyID   and   MaterialID   =   @MateriaID   and   StorehouseID   =   @StockHouse   and   LocID   =   @LocID   and   BatchNum   =   @BachID   and   StoragePeriod   =   @Duredate)
set   @Cost   =   @Cost   +   @Money
--   入库
if(@OpCode   =   1)
set   @Cost   =   @Cost   *   @IsRed   *   @InOut*1
--   出库
if(@OpCode   =   2)
set   @Cost   =   @Cost   *   @IsRed   *   @InOut*-1

set   @Basic   =   (select   BasicQty     from   IcInventory   where   AccountNO   =   @CompanyID   and   MaterialID   =   @MateriaID   and   StorehouseID   =   @StockHouse   and   LocID   =   @LocID   and   BatchNum   =   @BachID   and   StoragePeriod   =