写了一个存储过程
create proc proc_kaoqin
@EmployeeID varchar(20)
as
declare @time1 datetime
declare @time2 datetime
declare @time3 datetime
set @time1=null
set @time2=null
set @time3=null
begin
set @time1=(select AmUpCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select AmUpTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3<0)
begin
insert into CheckOn(EmployeeID,TodayDate,AmUpWorkState,AmLateTime) values(@EmployeeID,CONVERT(varchar(10), getdate(), 120),'正常',0)
end
else if(@time3>0)
begin
insert into CheckOn(EmployeeID,TodayDate,AmUpWorkState,AmLateTime) values(@EmployeeID,CONVERT(varchar(10), getdate(), 120),'迟到',@time3)
end
end
else if(@time1 is null)
begin
insert into CheckOn(EmployeeID,TodayDate,AmUpWorkState,AmLateTime) values(@EmployeeID,CONVERT(varchar(10), getdate(), 120),'旷工',@time3)
end
end
begin
begin
set @time1=(select AmDownCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select AmDownTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3>0)
begin
update CheckOn set AmDownWorkState = '正常' , AmEarlyTime=0 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
else if(@time3<0)
begin
update CheckOn set AmDownWorkState = '迟到' , AmEarlyTime=@time3 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
else if(@time1 is null)
begin
update CheckOn set AmDownWorkState = '旷工' , AmEarlyTime=null where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
end
begin
begin
set @time1=(select PmUpCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select PmUpTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3>0)
begin
update CheckOn set PmUpWorkState = '正常' , PmLateTime=0 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
else if(@time3<0)
begin
update CheckOn set PmUpWorkState = '迟到' , PmLateTime=@time3 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
else if(@time1 is null)
begin
update CheckOn set PmUpWorkState = '旷工' , PmLateTime=null where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
end
begin
begin
set @time1=(select PmDownCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select PmDownTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3>0)
begin
update CheckOn set PmDownWorkState = '正常' , PmEarlyTime=0 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
else if(@time3<0)
begin
update CheckOn set PmDownWorkState = '迟到' , PmEarlyTime=@time3 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
else if(@time1 is null)
begin
update CheckOn set PmDownWorkState = '旷工' , PmEarlyTime=NULL where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
end
创建成功
可是执行存储过程时却有错了。
exec proc_kaoqin '13'
报错：
消息 8115，级别 16，状态 2，过程 proc_kaoqin，第 14 行