ALTER PROCEDURE [dbo].[HRP_GetEmployeeMaxDays]
AS
BEGIN
declare workID cursor for select workID from employee
open workID
declare @workID int
declare @Maxdays int
declare @joindate datetime
fetch next from workID
into @workID
while @@Fetch_status = 0
if Datediff(y,@joindate ,getdate())> 1
begin
select @joindate =joindate from employee where workID=@workID
set @MaxDays =cast (datediff(M, @joindate ,getdate())*5/6 as int)
update leaves set MaxDays =@MaxDays where workID=@workID
fetch next from workID
into @workID
print '执行了'
end
close workID
deallocate workID
end EXEC HRP_GetEmployeeMaxDays调试一下看看
ALTER PROCEDURE [dbo].[HRP_GetEmployeeMaxDays]
AS
BEGIN
declare workID cursor
for select workID from employee
open workID
declare @workID int
declare @Maxdays int
declare @joindate datetime
fetch next from workID
into @workID
while @@Fetch_status =0
begin
if Datediff(y,@joindate ,getdate())> 1
begin
select @joindate =joindate from employee where workID=@workID
set @MaxDays =cast (datediff(M, @joindate ,getdate())*5/6 as int)
update leaves set MaxDays =@MaxDays where workID=@workID
end
fetch next from workID
into @workID
end
close workID
deallocate workID end
Datediff(y,@joindate ,getdate()) 永远返回null
fetch永远执行不到
,应该这么改ALTER PROCEDURE [dbo].[HRP_GetEmployeeMaxDays]
AS
BEGIN
declare workID cursor
for select workID from employee
open workID
declare @workID int
declare @Maxdays int
declare @joindate datetime set @joindate='1960-01-01'fetch next from workID
into @workID
while @@Fetch_status =0
begin
if Datediff(y,@joindate ,getdate())> 1
begin
select @joindate =joindate from employee where workID=@workID
set @MaxDays =cast (datediff(M, @joindate ,getdate())*5/6 as int)
update leaves set MaxDays =@MaxDays where workID=@workID
end
fetch next from workID
into @workID
end
close workID
deallocate workID end
ALTER PROCEDURE [dbo].[HRP_GetEmployeeMaxDays]
AS
BEGIN
declare workID cursor
for select workID from employee
open workID
declare @workID int
declare @Maxdays int
declare @joindate datetime
fetch next from workID
into @workID set @joindate='1960-01-01'while @@Fetch_status =0
begin
if Datediff(y,@joindate ,getdate())> 1
begin
select @joindate =joindate from employee where workID=@workID
set @MaxDays =cast (datediff(M, @joindate ,getdate())*5/6 as int)
update leaves set MaxDays =@MaxDays where workID=@workID
end
fetch next from workID
into @workID
end
close workID
deallocate workID end