求高手指点，原来存储过程执行正常的，现在报错了
USE [CardManage]GO
/****** Object: StoredProcedure [dbo].[Work_DataTransact] Script Date: 06/09/2015 15:07:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--'00000000-0000-0000-0000-000000000000'
ALTER PROCEDURE [dbo].[Work_DataTransact]
-- Add the parameters for the stored procedure here
@WorkDatetime Datetime--排班单内的某一天
AS
BEGIN
--临时表#WorkEmplClass 要处理的人员及班次
--临时表#WorkRecord 要处理人员的数据
--临时表#WorkEmplRecord 处理后的数据存入此表
--处理考勤每一天的数据
Declare @NextDay Bit -- 代表有跨天
Declare @OnDutyMin Int,@OffDutyMin Int,@OverStartTimeMin Int,@OverEndTimeMin Int
Declare @RestTime1StartMin Int,@RestTime1EndMin Int,@RestTime2StartMin Int,@RestTime2EndMin Int,@RestMin Int
Declare @OnDuty Datetime,@OffDuty Datetime,@OnDutyAdvanceTime Datetime,@OnDutyDelayTime Datetime,@OffDutyAdvanceTime Datetime,@OffDutyDelayTime Datetime
/*
Declare @ClassType NVarChar(2),@ClassName Varchar(50),@AdvanceOnDutyMin Int,@LateMinAbsence Int,@AllowLateMin Int,@EarlyMinAbsence Int,@DelayOffDutyMin Int,@AllowEarlyMin Int,@RestTime1Start Datetime,@RestTime1End Datetime,@RestTime2Start Datetime,@RestTime2End Datetime,@RestTimeAllowLDMin Int,@OverStartTime Datetime,@OverEndTime Datetime,@OverMinValid Int
Declare @Remark Varchar(50)
Declare @ClassNo uniqueidentifier,@WorkHours Numeric(10,1)
Declare @OverStarttimeEstimate varchar(5), @OverEndtimeEstimate varchar(5),@OverHoursEstimate Numeric(10,1)
*/
Declare @ClassType NVarChar(20),@ClassName Varchar(50),@AdvanceOnDutyMin Int,@LateMinAbsence Int,@AllowLateMin Int,@EarlyMinAbsence Int,@DelayOffDutyMin Int,@AllowEarlyMin Int,@RestTime1Start Datetime,@RestTime1End Datetime,@RestTime2Start Datetime,@RestTime2End Datetime,@RestTimeAllowLDMin Int,@OverStartTime Datetime,@OverEndTime Datetime,@OverMinValid Int
Declare @Remark Varchar(50)
Declare @ClassNo uniqueidentifier,@WorkHours Numeric(20,2)
Declare @OverStarttimeEstimate varchar(15), @OverEndtimeEstimate varchar(15),@OverHoursEstimate Numeric(20,2)
If @WorkDatetime>Getdate() goto execend
Select Top 1 @ClassNo=ClsNo From (Select Distinct ClsNo From #WorkEmplClass)A
While @@rowcount>0
Begin
Select @ClassType=ClassType,@ClassName=ClassName,@OnDuty=OnDuty,@OffDuty=OffDuty,@AdvanceOnDutyMin=AdvanceOnDutyMin,@LateMinAbsence=LateMinAbsence,@AllowLateMin=AllowLateMin,@EarlyMinAbsence=EarlyMinAbsence,@DelayOffDutyMin=DelayOffDutyMin,@AllowEarlyMin=AllowEarlyMin,@RestTime1Start=RestTimeStart,@RestTime1End=RestTimeEnd,@OverStartTime=OverStartTime,@OverEndTime=OverEndTime,@OverMinValid=OverMinValid From Work_Class Where SerialNo=@ClassNo
--清空本天内所操作人员的考勤记录
Select @Remark=HolidayName From Work_Holiday A Inner Join System_Serial B On A.SerialNo=B.SerialNo Where Status=1 And @WorkDatetime Between StartDate And EndDate
If @@Rowcount>0 --节假日
Insert #WorkEmplRecord Select ItemCode,EmplCode,EmplName,'节假日',@WorkDatetime,'','',0,0,0,0,'',0,0,@Remark,0 From #WorkEmplClass Where ClsNo=@ClassNo
Else IF @ClassType ='休息'
Insert #WorkEmplRecord Select ItemCode,EmplCode,EmplName,'休息日',@WorkDatetime,'','',0,0,0,0,'',0,0,@ClassName,0 From #WorkEmplClass Where ClsNo=@ClassNo
Else IF @ClassType ='工作'
Begin
--取出上班时间占当天的分钟数
Set @OnDutyMin= datediff(n,'00:00:00',Convert(Varchar(8),@OnDuty,108))
--取出下班时间占当天的分钟数
Set @OffDutyMin= datediff(n,'00:00:00',Convert(Varchar(8),@OffDuty,108))
--取出加班开始时间占当天的分钟数
Set @OverStartTimeMin= datediff(n,'00:00:00',Convert(Varchar(8),@OverStartTime,108))
--取出加班结束时间占当天的分钟数
Set @OverEndTimeMin= datediff(n,'00:00:00',Convert(Varchar(8),@OverEndTime,108))
--取出第一休息开始时间占当天的分钟数
Set @RestTime1StartMin= datediff(n,'00:00:00',Convert(Varchar(8),@RestTime1Start,108))
--取出第一休息结束时间占当天的分钟数
Set @RestTime1EndMin= datediff(n,'00:00:00',Convert(Varchar(8),@RestTime1End,108))
If @RestTime1StartMin<=@RestTime1EndMin
Set @RestMin=@RestTime1EndMin-@RestTime1StartMin
Else
Set @RestMin=@RestTime1EndMin+1440-@RestTime1StartMin
Set @NextDay=0
--下班分钟小于或等于上班分钟时表示为跨天,则增加一天分钟,即1440
If @OnDutyMin>=@OffDutyMin --20:00 07:00 --00:00 00:00|12:00 12:00
Begin
Set @OffDutyMin=@OffDutyMin+1440
Set @NextDay=1
End
--取出本天的上班时间:2008-11-10 07:00
Set @OnDuty=Dateadd(n,@OnDutyMin+@AllowLateMin,@WorkDatetime)
--取出本天的下班时间:2008-11-10 17:00
Set @OffDuty=Dateadd(n,@OffDutyMin-@AllowEarlyMin,@WorkDatetime)
--取出上下班的提前延后分钟
Set @OnDutyAdvanceTime=Dateadd(n,@OnDutyMin-@AdvanceOnDutyMin,@WorkDatetime)
Set @OnDutyDelayTime=Dateadd(n,@OnDutyMin+@LateMinAbsence,@WorkDatetime)
Set @OffDutyAdvanceTime=Dateadd(n,@OffDutyMin-@EarlyMinAbsence,@WorkDatetime)
Set @OffDutyDelayTime=Dateadd(n,@OffDutyMin+@DelayOffDutyMin,@WorkDatetime)
--有加班设置
If @OverStartTimeMin>0 Or @OverEndTimeMin>0
Begin
--select @OffDutyDelayTime,@NextDay,@OverEndTimeMin,@OverStartTimeMin,@OffDutyMin
If @NextDay=0 And @OverEndTimeMin>@OverStartTimeMin And @OverStartTimeMin>=@OffDutyMin
Begin
Set @OverStartTime=Dateadd(n,@OverStartTimeMin+@OverMinValid,@WorkDatetime)
Set @OverEndTime=Dateadd(n,@OverEndTimeMin,@WorkDatetime)
--select @OverEndTimeMin,@WorkDatetime
End
Else
Begin
If @OverEndTimeMin>=@OverStartTimeMin
Set @OverStartTime=Dateadd(n,@OverStartTimeMin+@OverMinValid+1440,@WorkDatetime)
Else
Set @OverStartTime=Dateadd(n,@OverStartTimeMin+@OverMinValid,@WorkDatetime)
Set @OverEndTime=Dateadd(n,@OverEndTimeMin+1440,@WorkDatetime)
End
Set @OffDutyDelayTime=@OverEndTime
End
--加班预计
Set @OverStarttimeEstimate=Convert(char(5),DateAdd(n,-@OverMinValid,@OverStartTime),108)
Set @OverEndtimeEstimate=Convert(Varchar(5),@OverEndTime,108)
Set @OverHoursEstimate=0
Set @WorkHours=Cast(ROUND(Cast(@OffDutyMin-@OnDutyMin-@RestMin As Numeric(10,1))/60,1)As Numeric(10,1))
--select @OffDutyAdvanceTime , @OffDutyDelayTime
--只保留要处理的天的数据
Select ItemCode,WorkDatetime Into #WorkRecordTmp From #WorkRecord Where WorkDatetime Between @OnDutyAdvanceTime and @OffDutyDelayTime
--取出每个人在上班段最早的时间及下班段最迟的时间
Insert #WorkEmplRecord
Select ItemCode,EmplCode,EmplName,@ClassName,@WorkDatetime,OnDutyTime,OffDutyTime,@WorkHours,
--上班分数等于下班时间减上班时间减休息分钟减迟到及早退分钟
WorkDutyHours=Case When OnDutyTime!='' and OffDutyTime!='' Then Cast(ROUND(Cast(@OffDutyMin-@OnDutyMin-@RestMin-LateMin-EarlyMin As Numeric(10,1))/60,1)As Numeric(10,1)) Else 0 End,LateMin,EarlyMin,Case When WorkOver=0 Then '' Else Convert(Char(5),Dateadd(n,-@OverMinValid,@OverStartTime),108) End,WorkOver,0,'',0 From
(
Select C.ItemCode,C.EmplCode,EmplName,isnull(Convert(Char(5),OnDutyTime,108),'')OnDutyTime,isnull(Convert(Char(5),OffDutyTime,108),'')OffDutyTime
--迟到:当上班时间为空或实际上班时间小于或等于规定的上班时间时表示无迟到
,LateMin=Case When OnDutyTime Is Null Or OnDutyTime<=@OnDuty Then 0 Else Datediff(n,@OnDuty,OnDutyTime)+@AllowLateMin End
--早退:当下班时间为空或实际上班时间小于或等于规定的上班时间时表示无迟到
,EarlyMin=Case When OffDutyTime Is Null Or @OffDuty<=OffDutyTime Then 0 Else abs(Datediff(n,@OffDuty,OffDutyTime)) End
,WorkOver=Case When OffDutyTime Between @OverStartTime And @OverEndTime Then Datediff(n,@OverStartTime,OffDutyTime)+@OverMinValid Else 0 End
From #WorkEmplClass C Left Outer Join
(Select ItemCode,Min(WorkDatetime)OnDutyTime From #WorkRecordTmp Where WorkDatetime Between @OnDutyAdvanceTime and @OnDutyDelayTime Group By ItemCode)A On C.ItemCode=A.ItemCode Left Outer Join
(Select ItemCode,Max(WorkDatetime)OffDutyTime From #WorkRecordTmp Where WorkDatetime Between @OffDutyAdvanceTime and @OffDutyDelayTime Group By ItemCode)B On C.ItemCode=B.ItemCode
Where ClsNo=@ClassNo)A
End
--将加班的记录存进加班表
Delete Work_RecordOver Where OverTimeDate=@WorkDatetime And ItemCode In(Select ItemCode From #WorkEmplClass Where ClsNo=@ClassNo)
Insert Work_RecordOver Select ItemCode,EmplCode,EmplName,WorkDate,@OverStarttimeEstimate,@OverEndtimeEstimate,@OverHoursEstimate,'',WorkOverStart,OffDutyTime,WorkOverMins,'',0 From #WorkEmplRecord Where WorkDate=@WorkDatetime And ItemCode In(Select ItemCode From #WorkEmplClass Where ClsNo=@ClassNo) And WorkOverMins>0
Delete #WorkEmplClass Where ClsNo=@ClassNO
Select Top 1 @ClassNo=ClsNo From (Select Distinct ClsNo From #WorkEmplClass)A
End
execend:
End
现在执行报错信息：
消息 8152，级别 16，状态 14，过程 Work_DataTransact，第 130 行
将截断字符串或二进制数据。
语句已终止。
