如题.
我有两张表,分别为表zj_EmployeeMovement和表zj_DutyRecord表zj_EmployeeMovement为员工的状态变动表,在职状态workState和岗位级别UserPosition经常会变动.结构如下:
ID int Unchecked
UserNo varchar(30) Checked
UserName varchar(30) Checked
DeptNo varchar(30) Checked
DeptName varchar(30) Checked
UserPosition varchar(50) Checked
WorkState varchar(30) Checked
MoveType varchar(30) Checked
StartTime datetime Checked
EndTime datetime Checked
Ins_Time datetime Checked
Mod_Time datetime Checked
Notes varchar(300) Checked
___________________________________________
表zj_DutyRecord为员工考勤表,每天都有考勤的,考勤表里包含员工每天的在职状态WorkStatue,和岗位级别PositionLevel表结构如下:
ID bigint Unchecked
DeptNo varchar(30) Checked
UserNo varchar(30) Unchecked
UserName varchar(30) Checked
DutyDate datetime Checked
DutyMorning varchar(40) Checked
DutyAfternoon varchar(40) Checked
WorkStatue varchar(30) Checked
PositionLevel varchar(30) Checked
DutyNotes varchar(80) Checked
Ins_Time datetime Checked
Mod_Time datetime Checked
Creator varchar(30) Checked
Modifyer varchar(30) Checked___________________________________________________
说明:考勤表里WorkStatue和PositionLevel来自另一个员工表(员工表不列出来了),员工这两个字段更改时,可能存在人为的时间上的滞后性,也就是说员工每天考勤时的考勤状态跟实际的不一样,所以引用员工的变动记录用zj_EmployeeMovement保存起来.这种变动记录修改时,又调用以下存储过程来修正历史考勤中的职位和岗位级别.问题是下面的存储过程会报错,而单独执行存储过程中的游标部门,又不会报错,有人能帮忙找找错吗?
存储过程如下:
____________________________________________________SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
Create PROCEDURE sp_SyncDutyRecord
(
@DestUserNo varchar(30) ='',
@ActResult int output
)
AS declare @UserNo varchar(30)
--set @UserNo=@DestUserNo
--起始游标
DECLARE CursorSourMovement CURSOR FOR
SELECT StartTime FROM zj_EmployeeMovement where UserNo=@UserNo order by Ins_Time desc --目标游标
DECLARE CursorDestMovement CURSOR FOR
SELECT WorkState,UserPosition,StartTime FROM zj_EmployeeMovement where UserNo=@UserNo order by Ins_Time desc
GO --打开游标
open CursorSourMovement
open CursorDestMovement --定义变量
declare @WorkState varchar(30),@UserPosition varchar(30),@StartTime DateTime
declare @EndTime DateTime
--设定初始游标位置和变量的值
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime --遍历所有的记录
while(@@fetch_status=0)
begin
--print '状态: ' + @WorkState + ' 职位: '+@UserPosition
--print '开始时间'+cast(@StartTime as varchar(30))+ @WorkState+ ' '+@UserPosition
--print '结束时间'+cast(@EndTime as varchar(30))
--更新某个时间区段不同职位或者职位的考勤
update zj_DutyRecord set WorkState=@WorkState ,PositionLevel=@UserPosition where DutyDate between @StartTime and @EndTime
--移动游标
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime
end --关闭游标
close CursorSourMovement
close CursorDestMovement --删除游标
deallocate CursorSourMovement
deallocate CursorDestMovement select @ActResult=1GO
我有两张表,分别为表zj_EmployeeMovement和表zj_DutyRecord表zj_EmployeeMovement为员工的状态变动表,在职状态workState和岗位级别UserPosition经常会变动.结构如下:
ID int Unchecked
UserNo varchar(30) Checked
UserName varchar(30) Checked
DeptNo varchar(30) Checked
DeptName varchar(30) Checked
UserPosition varchar(50) Checked
WorkState varchar(30) Checked
MoveType varchar(30) Checked
StartTime datetime Checked
EndTime datetime Checked
Ins_Time datetime Checked
Mod_Time datetime Checked
Notes varchar(300) Checked
___________________________________________
表zj_DutyRecord为员工考勤表,每天都有考勤的,考勤表里包含员工每天的在职状态WorkStatue,和岗位级别PositionLevel表结构如下:
ID bigint Unchecked
DeptNo varchar(30) Checked
UserNo varchar(30) Unchecked
UserName varchar(30) Checked
DutyDate datetime Checked
DutyMorning varchar(40) Checked
DutyAfternoon varchar(40) Checked
WorkStatue varchar(30) Checked
PositionLevel varchar(30) Checked
DutyNotes varchar(80) Checked
Ins_Time datetime Checked
Mod_Time datetime Checked
Creator varchar(30) Checked
Modifyer varchar(30) Checked___________________________________________________
说明:考勤表里WorkStatue和PositionLevel来自另一个员工表(员工表不列出来了),员工这两个字段更改时,可能存在人为的时间上的滞后性,也就是说员工每天考勤时的考勤状态跟实际的不一样,所以引用员工的变动记录用zj_EmployeeMovement保存起来.这种变动记录修改时,又调用以下存储过程来修正历史考勤中的职位和岗位级别.问题是下面的存储过程会报错,而单独执行存储过程中的游标部门,又不会报错,有人能帮忙找找错吗?
存储过程如下:
____________________________________________________SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
Create PROCEDURE sp_SyncDutyRecord
(
@DestUserNo varchar(30) ='',
@ActResult int output
)
AS declare @UserNo varchar(30)
--set @UserNo=@DestUserNo
--起始游标
DECLARE CursorSourMovement CURSOR FOR
SELECT StartTime FROM zj_EmployeeMovement where UserNo=@UserNo order by Ins_Time desc --目标游标
DECLARE CursorDestMovement CURSOR FOR
SELECT WorkState,UserPosition,StartTime FROM zj_EmployeeMovement where UserNo=@UserNo order by Ins_Time desc
GO --打开游标
open CursorSourMovement
open CursorDestMovement --定义变量
declare @WorkState varchar(30),@UserPosition varchar(30),@StartTime DateTime
declare @EndTime DateTime
--设定初始游标位置和变量的值
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime --遍历所有的记录
while(@@fetch_status=0)
begin
--print '状态: ' + @WorkState + ' 职位: '+@UserPosition
--print '开始时间'+cast(@StartTime as varchar(30))+ @WorkState+ ' '+@UserPosition
--print '结束时间'+cast(@EndTime as varchar(30))
--更新某个时间区段不同职位或者职位的考勤
update zj_DutyRecord set WorkState=@WorkState ,PositionLevel=@UserPosition where DutyDate between @StartTime and @EndTime
--移动游标
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime
end --关闭游标
close CursorSourMovement
close CursorDestMovement --删除游标
deallocate CursorSourMovement
deallocate CursorDestMovement select @ActResult=1GO
解决方案 »
- C#控件!
- C# 调用WinRAR压缩目录
- 如何将窗体form1的变量x的值显示在窗体form2的文本框中?如果x的值变化显示在窗体form2的文本框中的也跟着变化?
- Dictionary,collection,list,arraylist等等,都有什么区别啊?
- 为何MemoryStream使用GZip压缩的输入流不行?
- 我要查询字符串中是否存在某个子串,用啥函数?
- 怎样才能判断dataSet中的二进制字段是否为空呢?
- 数字格式
- 【求助在线等】谁帮忙看下我这个函数为什么会使用错误VS2010 C#
- 有类似:MyDay = Day("October 19, 1962")提取年月日时分的C#系统自带的代码吗?
- 新手求助 ...清高手看下这段C#代码哪里有错...
- 如何在子窗口對父窗口的GirdView操作?
--设定初始游标位置和变量的值
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime --遍历所有的记录
while(@@fetch_status=0)
begin
--print '状态: ' + @WorkState + ' 职位: '+@UserPosition
--print '开始时间'+cast(@StartTime as varchar(30))+ @WorkState+ ' '+@UserPosition
--print '结束时间'+cast(@EndTime as varchar(30))
--更新某个时间区段不同职位或者职位的考勤
update zj_DutyRecord set WorkState=@WorkState ,PositionLevel=@UserPosition where DutyDate between @StartTime and @EndTime
--移动游标
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime
end 我修改了下,你试下。未测试
Create PROCEDURE sp_SyncDutyRecord
(
@DestUserNo varchar(30) ='',
@ActResult int output
)
AS Begin -- A declare @UserNo varchar(30)
--定义变量
declare @WorkState varchar(30),@UserPosition varchar(30),@StartTime DateTime
declare @EndTime DateTime --set @UserNo=@DestUserNo
--起始游标
DECLARE CursorSourMovement CURSOR FOR
SELECT StartTime FROM zj_EmployeeMovement where UserNo=@UserNo order by Ins_Time desc --目标游标
DECLARE CursorDestMovement CURSOR FOR
SELECT WorkState,UserPosition,StartTime FROM zj_EmployeeMovement where UserNo=@UserNo order by Ins_Time desc --打开游标
open CursorSourMovement
open CursorDestMovement WHILE (1=1)
BEGIN
--设定初始游标位置和变量的值
fetch next from CursorSourMovement into @EndTime
fetch next from CursorDestMovement into @WorkState,@UserPosition,@StartTime if @@fetch_status !=0 break;
--print '状态: ' + @WorkState + ' 职位: '+@UserPosition
--print '开始时间'+cast(@StartTime as varchar(30))+ @WorkState+ ' '+@UserPosition
--print '结束时间'+cast(@EndTime as varchar(30))
--更新某个时间区段不同职位或者职位的考勤
update zj_DutyRecord set WorkState=@WorkState ,PositionLevel=@UserPosition where DutyDate between @StartTime and @EndTime end --关闭游标
close CursorSourMovement
close CursorDestMovement --删除游标
deallocate CursorSourMovement
deallocate CursorDestMovement select @ActResult=1
return @ActResultGO