sqlserver 存储过程时运行显示 资源不足部分结果已除去(在线急)

Question

2005以上CTE可以满足大部分的要求

Answer 1

试帖出你的sql语句，我觉得不要那么用，影响速度

Answer 2

CREATE PROCEDURE zb_wghj2AS
 
declare @rq1 nvarchar(20)
declare @rq2 nvarchar(20)
declare @rq3 datetime
 
set @rq1=convert(nvarchar(20),dateadd(mm,-1,getdate()),112)set @rq2=convert(nvarchar(20),getdate(),112)delete from wgtj where convert(nvarchar(20),f_readdate,112)<=@rq2 and convert(nvarchar(20),f_readdate,112)>=@rq1
 
declare @f_workno  nvarchar(10)
declare @f_readdate datetime 
declare @f_readername nvarchar(50)
declare @strsql nvarchar(250)
declare @f_readdate1 datetime 
declare @f_readername1 nvarchar(50)
declare @f_readdate2 datetime 
declare @f_readername2 nvarchar(50)
declare @rq nvarchar(20)
declare @wgsj  float
declare @kk int
declare @f_consumerid int  
declare zb_cursor cursor for select  distinct f_workno ,f_consumerid from ycjl where convert(nvarchar(10),f_readdate,112)<=@rq2  and
 convert(nvarchar(20),f_readdate,112)>=@rq1 and bz not like '因公%'
set @wgsj=0declare @strsql1 nvarchar(4000)
open zb_cursorfetch next from zb_cursor into @f_workno,@f_consumerid while @@fetch_status=0
Begindeclare zb_cursor3  cursor for   select  distinct convert(nvarchar(20),f_readdate,112)  from  ycjl  where   bz not like '因公%'  and f_workno=@f_workno  and convert(nvarchar(20),f_readdate,112)<=@rq2 and convert(nvarchar(20),f_readdate,112)>=@rq1  and bz not like '因公%' order by convert(nvarchar(20),f_readdate,112)
open zb_cursor3
fetch next from zb_cursor3 into @rq
 
 
while  @@fetch_status=0begin 
         
declare  zb_cursor1 cursor for select  f_readdate,f_readername from ycjl where  (f_readername like '广场%' or f_readername like '正门%' ) and  convert(char(10),f_readdate,112)=@rq   and  f_workno=@f_workno  and bz  not like '因公%' order by f_readdateexec(@strsql1)  
        open zb_cursor1
 set @wgsj=0
      fetch next from zb_cursor1  into @f_readdate,@f_readername --print @f_readername +convert(nvarchar(100),@f_readdate)  +convert(nvarchar(20), @f_consumerid)
      while @@fetch_status=0  
begin 
                              
                       set @kk=charindex('[',@f_readername)                    
                 
                       if substring(@f_readername,@kk+1,2)='进门'                      ----------第一次进门默认为迟到
                               begin  
                                  if convert(char(10),@f_readdate,108)> '13:30:00'
                                       begin     
                                         set @wgsj=@wgsj+datediff(n,'13:30:00',convert(char(10),@f_readdate,108))        
                                       --  print 'a00b'+convert(nvarchar(133),@wgsj)
                                        end 
else
                                      begin                                           if convert(char(10),@f_readdate,108)< '12:30:00'  or  convert(char(10),@f_readdate,108)> '13:30:00'
                                            
                                           begin 
                                       set @wgsj=@wgsj+datediff(n,'8:30:00',convert(char(10),@f_readdate,108))
                                      -- print 'a01b'+convert(nvarchar(133),@wgsj)
                                           end                                     end 
                           fetch next from zb_cursor1 into @f_readdate2,@f_readername2
set  @f_readdate=@f_readdate2
                               set @f_readername=@f_readername2 
                                                                   
end 
                       else                                    
                             begin 
                                  fetch next from zb_cursor1 into @f_readdate1,@f_readername1
                                 
                                  
                                            if @@fetch_status<>0                        ----打卡出门未进门
                                                    begin 
                                                       if convert(char(10),@f_readdate,108)> '13:30:00'
                                                           begin 
 set @wgsj=@wgsj+datediff(n,convert(char(10),@f_readdate,108),'17:30:00')  
                                                           
                                                            end  
                                                        else
                                                           begin
                        if convert(char(10),@f_readdate,108)> '12:30:00'  and convert(char(10),@f_readdate,108)< '13:30:00'                              begin 
set @wgsj=@wgsj+datediff(n,'13:30:30','17:30:00') 
                              end 
else 
  begin 
   (select f_consumerid  From  t_d_leave   where   f_ConsumerID = @f_ConsumerID and convert(nvarchar(20),convert(datetime,f_value),112)=@rq and f_value1='上午' )
  UNION 
  (SELECT F_CONSUMERID FROM t_d_cardrecord where  f_consumerid=@f_consumerid and CONVERT(nvarchar(20),f_readdate,112)=@rq)
     
     if @@ROWCOUNT <>0
         begin
           set @wgsj=@wgsj+datediff(n,convert(char(10),@f_readdate,108),'12:30:00')         end
     else
     begin  
set @wgsj=@wgsj+datediff(n,convert(char(10),@f_readdate,108),'17:30:00')-60
     end 
                                                       --     print 'a03b'+convert(nvarchar(133),@wgsj)
 
end 
                                                           end 
                                                    end 
                                           else                                 --- if @@fetch_status<>0
 begin                                                      set @kk=charindex('[',@f_readername1)                 While  substring(@f_readername1,@kk+1,2)='出门' and @@FETCH_STATUS =0
                  begin 
                 if  @@FETCH_STATUS =0
                   begin 
               fetch next from zb_cursor1 into @f_readdate1,@f_readername1
               set @kk=charindex('[',@f_readername1) 
                  end 
                 end
---如果下一条记录是进门的统计  set @f_readdate2=@f_readdate1
  set @f_readername2=@f_readername1
 
   set @kk=charindex('[',@f_readername2)  
             while substring(@f_readername2,@kk+1,2)='进门' and @@FETCH_STATUS =0
                begin 
                     set @f_readdate1=@f_readdate2
                fetch next from zb_cursor1 into @f_readdate2,@f_readername2
                 set @kk=charindex('[',@f_readername2)
             end
                                                                  if (convert(char(10),@f_readdate,108)<'12:30:00'  and convert(char(10),@f_readdate1,108)<'12:30:00') or (convert(char(10),@f_readdate,108)>'13:30:00'  and convert(char(10),@f_readdate1,108)>'13:30:00')
                                                               begin
                                                                  set @wgsj=@wgsj+datediff(n, @f_readdate,@f_readdate1)  
 --print 'a04b'+convert(nvarchar(133),@wgsj)
     
                                                               end   
else
                                                               begin 
                                                             if   (convert(char(10),@f_readdate,108)<'12:30:00'  and convert(char(10),@f_readdate1,108)<'13:30:00') 
                                                               begin 
                                                                  set @wgsj=@wgsj+datediff(n,convert(char(10),@f_readdate,108),'12:30:30')
 --print 'a05b'+convert(nvarchar(133),@wgsj)                                                               end   
else
                                                               begin 
                                                                  if   (convert(char(10),@f_readdate,108)<'12:30:00'  and convert(char(10),@f_readdate1,108)>'13:30:00') 
                                                                    begin 
 set @wgsj=@wgsj+datediff(n,@f_readdate,@f_readdate1)-60
 --print 'a06b'+convert(nvarchar(133),@wgsj)                                                                     end      
 else
begin 
  if (convert(char(10),@f_readdate,108)>'12:30:00'  and convert(char(10),@f_readdate1,108)>'13:30:00') 
                                              
                                                                     begin 
                                                                    
  set @wgsj=@wgsj+datediff(n,'13:30:30',convert(char(10),@f_readdate1,108))
-- print 'a07b'+convert(nvarchar(133),@wgsj)                                                                  
                                                                     end    
end 
end                                                               end 
                                                     
                                                      set  @f_readdate=@f_readdate2
                               set @f_readername=@f_readername2 
                                                      end 
                                                    
                                                       
                                                       end                                                    
                                               
                               
                              
                                 
                              end 
                                
                                              close zb_cursor1 
                        DEALLOCATE zb_cursor1
                                                 
                 if @wgsj<>0.0 
begin 
 set @strsql='insert into wgtj(f_workno,f_readdate,wgsj,bz) values('''+@f_workno +''','''+convert(nvarchar(100),@f_readdate,102)+''','+convert(nvarchar(10),@wgsj)+',''门岗'')'
exec (@strsql)
end            
  fetch next from zb_cursor3 into @rq
end 
close zb_cursor3
deallocate zb_cursor3
  fetch next from zb_cursor into @f_workno,@f_consumerid
 end 
close zb_cursor 
deallocate zb_cursor
这是我的存储过程

Answer 3

参考 http://bbs.csdn.net/topics/310084218检查存储过程中,是否有大量的select..显示返回结果出来,一般是没有意义的,注释掉后再试试.