| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 832 人关注过本帖
标题:有谁帮我看一下这个存储过程吗,点执行一直处于执行状态!执行了半个小时还 ...
只看楼主 加入收藏
hong293
Rank: 1
等 级:新手上路
帖 子:9
专家分:5
注 册:2012-7-16
结帖率:0
收藏
已结贴  问题点数:20 回复次数:5 
有谁帮我看一下这个存储过程吗,点执行一直处于执行状态!执行了半个小时还在执行
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
   
--调整考勤   
ALTER  procedure [dbo].[Proc_UpdateKq]   
@Yearmonth as varchar(7)   
as     
  Declare @FirstDate as smallDatetime      --月初第一天   
  Declare @employeeid as int          --员工ID   
  Declare @RestType as int            --假日类别   
  Declare @checkDate as smalldatetime --考勤日期   
/*   
RestType --假日类型   
OnOffDutyData --考勤档案表   
RestType=6--1-5有一天为 6   
RestType=5--周6更新为5   
*/   
   set @FirstDate=@Yearmonth +'-01'   
   
   Declare Cur Cursor for      
      select Employeeid,checkDate from OnOffDutyData  where Datediff(month,CheckDate,@FirstDate)=0 and RestType=6 and (Datepart(dw,checkdate) between 2 and 6) and employeeid in (select employeeid from employeemsg where department in (select departmentid from datadepartment where preCode='CY')) order by Employeeid,checkDate   
   Open Cur   
     fetch next from cur into @employeeid,@checkDate   
     While (@@Fetch_Status=0)      
       begin      
               
             update OnOffDutyData set  RestType=5,classsouce=3 where employeeid=@employeeid and Datepart(dw,checkdate)=7 and isnull(RestType,0)=0 and (Datepart(wk,checkdate)=Datepart(wk,@checkdate))     
   
                  
       fetch next from cur into @employeeid,@checkDate   
   end            
   
 Close cur        
       Deallocate cur   
   


 



   

搜索更多相关主题的帖子: procedure 考勤 
2012-07-16 11:48
hong293
Rank: 1
等 级:新手上路
帖 子:9
专家分:5
注 册:2012-7-16
收藏
得分:0 
求解啊   
2012-07-16 12:25
qingshuiliu
Rank: 10Rank: 10Rank: 10
等 级:贵宾
威 望:17
帖 子:323
专家分:1538
注 册:2009-12-28
收藏
得分:10 
select Employeeid,checkDate from OnOffDutyData  where Datediff(month,CheckDate,@FirstDate)=0 and RestType=6 and (Datepart(dw,checkdate) between 2 and 6) and employeeid in (select employeeid from employeemsg where department in (select departmentid from datadepartment where preCode='CY')) order by Employeeid,checkDate
这一段,你手动执行一下,看看执行情况。

2012-07-16 16:43
netlin
Rank: 13Rank: 13Rank: 13Rank: 13
等 级:贵宾
威 望:24
帖 子:544
专家分:4308
注 册:2012-4-9
收藏
得分:10 
3楼的做法是正解。
先把你的存储过程的主体部分拿到查询分析器中运行,调试好后再放回存储过程中。
对于主体部分比较大的,还可以分段进行调试。
如下:

declare @Yearmonth as varchar(7)   
set @Yearmonth='xxxx-xx'   
     
  Declare @FirstDate as smallDatetime      --月初第一天   
  Declare @employeeid as int          --员工ID   
  Declare @RestType as int            --假日类别   
  Declare @checkDate as smalldatetime --考勤日期   
/*   
RestType --假日类型   
OnOffDutyData --考勤档案表   
RestType=6--1-5有一天为 6   
RestType=5--周6更新为5   
*/   
   set @FirstDate=@Yearmonth +'-01'   
   
   Declare Cur Cursor for      
      select Employeeid,checkDate from OnOffDutyData  where Datediff(month,CheckDate,@FirstDate)=0 and RestType=6 and (Datepart(dw,checkdate) between 2 and 6) and employeeid in (select employeeid from employeemsg where department in (select departmentid from datadepartment where preCode='CY')) order by Employeeid,checkDate   
   Open Cur   
     fetch next from cur into @employeeid,@checkDate   
     While (@@Fetch_Status=0)      
       begin      
               
             update OnOffDutyData set  RestType=5,classsouce=3 where employeeid=@employeeid and Datepart(dw,checkdate)=7 and isnull(RestType,0)=0 and (Datepart(wk,checkdate)=Datepart(wk,@checkdate))     
   
                  
       fetch next from cur into @employeeid,@checkDate   
   end      

做自己喜欢的事!
2012-07-17 07:38
hong293
Rank: 1
等 级:新手上路
帖 子:9
专家分:5
注 册:2012-7-16
收藏
得分:0 
回复 2楼 hong293
你一段没什么问题,执行出来有二千多条记录!
2012-07-18 10:57
hong293
Rank: 1
等 级:新手上路
帖 子:9
专家分:5
注 册:2012-7-16
收藏
得分:0 
回复 3楼 qingshuiliu
谢谢你的指导哦,我在  

 set @FirstDate=@Yearmonth +'-01'   后面插入一个select Employeeid,checkDate from OnOffDutyData  where Datediff(month,CheckDate,@FirstDate)=0 and RestType=6 and (Datepart(dw,checkdate) between 2 and 6) and employeeid in (select employeeid from employeemsg where department in (select departmentid from datadepartment where preCode='CY')) order by Employeeid,checkDate   
到临时表去,执行之后可以了呵呵
2012-07-18 11:09
快速回复:有谁帮我看一下这个存储过程吗,点执行一直处于执行状态!执行了半个小 ...
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.016608 second(s), 7 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved