小弟再发存储过程,小弟跪求各位老大的帮助!!!!!!!

Question

 alter procedure  jhdwjehzqgj
    @qssj datetime ,
    @zzsj datetime   
as      
      set   @qssj=cast(@qssj  as  varchar(20))
      set      @zzsj=cast(@qssj as  varchar(20))
        declare  @err  int  
       declare   @dwh  varchar(20)
        declare    @gr  varchar(20)
        declare    @pf   varchar(20)
        declare  @ls   varchar(20)
        declare @rl   varchar(20)
     
   if   object_id('tempdb..#jhhzba')  is not  null   drop   table  #jhhzba
       
   if   @@error<> 0
        begin
             select  @err=1  ----删除 #jhhz 不成功
             return
        end
 create  table  #jhhzb
 (dwh varchar(5),grje varchar(20), pfje varchar(20),lsje varchar(20) ) ---创建一个临时表 declare   jeyb scroll  cursor  for ---声明游标
       select  yk_istoreroom.stucode, sum(yk_istoreroom.phprice*yk_istoreroom.amount/codex.cvrate)as  购入金额 ,
                                sum(yk_istoreroom.wsprice*yk_istoreroom.amount/codex.cvrate) as 批发金额 ,
                                sum(yk_istoreroom.rtprice*yk_istoreroom.amount)as 零售金额   ,
       sum(yk_istoreroom.wsprice*yk_istoreroom.amount/codex.cvrate-yk_istoreroom.phprice*yk_istoreroom.amount/codex.cvrate)
       as 让利金额
           
       from yk_istoreroom,codex,stunits
       where   auditdate >=@qssj and  auditdate <=@zzsj
                  and codex.medcode=yk_istoreroom.medcode and  stunits.stucode=yk_istoreroom.stucode
       group by yk_istoreroom.stucode open  jeyb     ---打开游标
 fetch next  from  jeyb  into @dwh, @gr, @pf, @ls, @rl
 select stucode=@dwh, 购入金额=@gr,批发金额=@pf, 零售金额=@ls, 让利金额=@rl  into  #jhhzba
   while   @@fetch_status=0
     begin
          fetch next  from  jeyb  into @dwh, @gr, @pf, @ls, @rl
if @@fetch_status=0
insert #jhhzba select @dwh, @gr, @pf, @ls, @rl 
     end
  close  jeyb
  deallocate  jeyb
 select  * From  #jhhzba
      --- 调用示例
---exec jhdwjehzqgj   '2004-08-25 00:00:00','2004-08-27 00:00:00'

Answer 1

--这样和上面的结果是一样的.
 alter procedure  jhdwjehzqgj
    @qssj datetime ,
    @zzsj datetime   
as      
      set   @qssj=cast(@qssj  as  varchar(20))
      set      @zzsj=cast(@qssj as  varchar(20))
        declare  @err  int  
     
      
   if   @@error<> 0
        begin
             select  @err=1  ----删除 #jhhz 不成功
             return
        end       select  yk_istoreroom.stucode, sum(yk_istoreroom.phprice*yk_istoreroom.amount/codex.cvrate)as  购入金额 ,
                                sum(yk_istoreroom.wsprice*yk_istoreroom.amount/codex.cvrate) as 批发金额 ,
                                sum(yk_istoreroom.rtprice*yk_istoreroom.amount)as 零售金额   ,
       sum(yk_istoreroom.wsprice*yk_istoreroom.amount/codex.cvrate-yk_istoreroom.phprice*yk_istoreroom.amount/codex.cvrate)
       as 让利金额
       from yk_istoreroom,codex,stunits
       where   auditdate >=@qssj and  auditdate <=@zzsj
                  and codex.medcode=yk_istoreroom.medcode and  stunits.stucode=yk_istoreroom.stucode
       group by yk_istoreroom.stucode      --- 调用示例
---exec jhdwjehzqgj   '2004-08-25 00:00:00','2004-08-27 00:00:00'

Answer 2

皱建老师 ,你的第一个方法的  :
  if @@fetch_status=0
insert #jhhzba select @dwh, @gr, @pf, @ls, @rl 
     
  是不是有什么不妥的地方呀?还是小弟太菜了,不理解呀?

Answer 3

我用你的两种方法执行存储过程时,结果是空的.但是我直接用select 语句查询时,却可以得到结果集.是不是存储过程的别的方面还有问题呀??