急呀!求救...存储过程问题

Question

执行时出错为
消息 16958，级别 16，状态 3，过程 PROC_Sale_StockToIntExamine，第 140 行
无法完成游标操作，因为在声明该游标后，所设置的选项发生了更改。没有事务,事务在程序中实现
代码如下:
-- =============================================
-- 进货或进货退货以及库存期初单据审批与取消审批
-- =============================================
ALTER PROCEDURE [dbo].[PROC_Sale_StockToIntExamine]
@BillID bigint,             --单据ID
@BillTypeID int,            --单据类型ID
@ExamineManID int,          --审批人ID
@ExamineType int            --审批类型(1是审批,-1或其他是取消审批)
AS
BEGIN
  declare @FixtureInfoID bigint  --设备ID
  declare @Number int            --数量
  declare @StorehouseID int      --仓库ID  declare @OrganiseID int        --组织机构ID
  declare @TheDate smalldatetime --单据日期
  declare @TempDate smalldatetime --临时日期
  declare @strTempDate varchar(50) --临时文本日期
  declare @YearID int            --年度ID
  declare @MonthID int           --月度ID
  declare @ErrInfo varchar(50)   --错误信息
 
  declare @iCount int            --查看库存账中是否存在
  declare @tempNum int           --临时统计是否已经存在库在  select @OrganiseID=ISNULL(OrganiseID,0),@TempDate=TheDate FROM Sale_O_StockMain WHERE BillID=@BillID
    
  
  if(@OrganiseID<>0)
    BEGIN
       if(@TempDate is null) 
        BEGIN
           raiserror ('单据日期为空,必须修改单据后才能操作!',16,1)
           return
        END       if(@BillTypeID<>701)  --不等于库存期初时
          set @TheDate=@TempDate 
       else
         BEGIN 
           if(Month(@TempDate)<>12)  --如果等于库存期时，就应该为下月的1月
               BEGIN
                 set @strTempDate=Convert(varchar,YEAR(@TempDate)) + '-' + Convert(varchar,Month(@TempDate) + 1) + '-01'
               END
             else
               BEGIN
                  set @strTempDate=Convert(varchar,YEAR(@TempDate)+ 1) + '-01-01'
               END
            set @TheDate=Convert(smalldatetime,@strTempDate)
         END
     
       set @YearID=YEAR(@TheDate)   
       set @MonthID=Month(@TheDate)        declare xs_Stcok cursor    --定义游标
       for select FixtureInfoID,Number,StorehouseID from Sale_O_StockList WHERE BillID=@BillID
       open xs_Stcok    --打开游标
       
       fetch next from xs_Stcok into @FixtureInfoID,@Number,@StorehouseID
        WHILE(@@fetch_status=0)
          BEGIN
             select @iCount=ISNULL(count(*),0) FROM Sale_S_TotalAccount WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID         
             if (@iCount=0)   --还没有发生库存总账
              BEGIN
                if(@ExamineType=1)  --审批
                   BEGIN
                      if(@BillTypeID=701)  --库存期初 
                         INSERT Sale_S_TotalAccount(FixtureInfoID,OrganiseID,StorehouseID,YearID,MonthID,BeginNum) VALUES(@FixtureInfoID,@OrganiseID,@StorehouseID,@YearID,@MonthID,@Number)
                       else if(@BillTypeID=708)  --进货
                         INSERT Sale_S_TotalAccount(FixtureInfoID,OrganiseID,StorehouseID,YearID,MonthID,IncomeNum) VALUES(@FixtureInfoID,@OrganiseID,@StorehouseID,@YearID,@MonthID,@Number)
                       else if(@BillTypeID=711)  --进货退货
                         INSERT Sale_S_TotalAccount(FixtureInfoID,OrganiseID,StorehouseID,YearID,MonthID,IncomeNum) VALUES(@FixtureInfoID,@OrganiseID,@StorehouseID,@YearID,@MonthID,@Number*-1)
                       else
                        BEGIN
                          CLOSE xs_Stcok 
                          DEALLOCATE xs_Stcok  
                          raiserror ('只能在存货期初\进货\进货退货单中进行审批!',16,1)
                          return 
                        END
                   END
                 else       --取消审批
                   BEGIN
                     CLOSE xs_Stcok 
                     DEALLOCATE xs_Stcok  
                     raiserror ('发生严重的登账错误,单据中一个或多个没有登库存总账,却取消审批,请检查单据!',16,1)
                     return 
                   END
              END  
             ELSE   --已经发生为存总账
              BEGIN
                set ansi_warnings off 
                select @tempNum=(ISNULL(SUM(BeginNum),0) + ISNULL(SUM(IncomeNum),0) - ISNULL(SUM(SentOutNum),0)) FROM Sale_S_TotalAccount WHERE FixtureInfoID=@FixtureInfoID
                if(@ExamineType=1)  --审批
                   BEGIN   
                      if(@tempNum<>0)
                        BEGIN
                          CLOSE xs_Stcok 
                          DEALLOCATE xs_Stcok  
                          raiserror ('单据中已经有存在库存的设备!',16,1)
                          return 
                        END                    
                      if(@BillTypeID=701)  --库存期初 
                         UPDATE Sale_S_TotalAccount SET BeginNum=ISNULL(BeginNum,0) + @Number WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID 
                       else if(@BillTypeID=708)  --进货
                         UPDATE Sale_S_TotalAccount SET IncomeNum=ISNULL(IncomeNum,0) + @Number WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID 
                       else if(@BillTypeID=711)  --进货退货
                         UPDATE Sale_S_TotalAccount SET IncomeNum=ISNULL(IncomeNum,0) - @Number WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID 
                       else
                        BEGIN
                          CLOSE xs_Stcok 
                          DEALLOCATE xs_Stcok  
                          raiserror ('只能在存货期初\进货\进货退货单中进行审批!',16,1)
                          return 
                        END
                   END
                 else       --取消审批
                   BEGIN
                     if(@tempNum<>1)
                        BEGIN
                          CLOSE xs_Stcok 
                          DEALLOCATE xs_Stcok  
                          raiserror ('单据中已经设备出库,不能再取消审批出库!',16,1)
                          return 
                        END        
                     if(@BillTypeID=701)  --库存期初 
                         DELETE FROM Sale_S_TotalAccount WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID 
                       else if(@BillTypeID=708)  --进货
                         DELETE FROM Sale_S_TotalAccount WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID 
                       else if(@BillTypeID=711)  --进货退货
                         DELETE FROM Sale_S_TotalAccount WHERE FixtureInfoID=@FixtureInfoID AND OrganiseID=@OrganiseID AND StorehouseID=@StorehouseID AND YearID=@YearID AND MonthID=@MonthID 
                       else
                        BEGIN
                          CLOSE xs_Stcok 
                          DEALLOCATE xs_Stcok  
                          raiserror ('只能在存货期初\进货\进货退货单中进行审批!',16,1)
                          return 
                        END
                   END
              END  
             fetch next from xs_Stcok into @FixtureInfoID,@Number,@StorehouseID
          END
       CLOSE xs_Stcok 
       DEALLOCATE xs_Stcok     
   END
else
   BEGIN
     raiserror ('不存在的单据,请刷新再操作!',16,1)
     return
   END
END

Answer 1

审批时没问题,即登台账没问题,但取消审批时(从台账中更改或删除已登台账)就出错!消息   16958，级别   16，状态   3，过程   PROC_Sale_StockToIntExamine，第   140   行 
无法完成游标操作，因为在声明该游标后，所设置的选项发生了更改。

Answer 2

就算把所有关闭、释放以返回语句删除也一样出问题CLOSE   xs_Stcok                                                       
DEALLOCATE   xs_Stcok     

Answer 3

删除这一行试试　　set   ansi_warnings   off 

Answer 4

行了,就是这一行set ansi_warnings off 可能是我刚才没调试好