CREATE PROCEDURE sp_LeaveDayBatch(
@year char(4)
) AS
declare @EmployID varchar(10),
@DateOnJob as datetime,
@DateAvailable as datetime,
@DateExpire as datetime,
@LeaveDaysAvailable as int
declare authors_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR
SELECT MV001,
CONVERT(datetime,MV021,112) as DateOnJob,
CONVERT(datetime,@year+right(MV021,4),112) as DateAvailable,
dateadd(day,-1,convert(datetime,CAST(CAST(@year as int) + 1 AS char(4)) + right(MV021,4),112)) as DateExpire,
dbo.getdays(year(convert(datetime, MV021, 112)), cast(@year as numeric(4))) as LeaveDaysAvailable
FROM [172.16.200.81].Leader.dbo.CMSMV
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
into @EmployID,
@DateOnJob,
@DateAvailable,
@DateExpire,
@LeaveDaysAvailable
WHILE (@@FETCH_STATUS = 0 )
BEGIN
INSERT INTO CMSMV (EmployID, DateOnJob, DateAvailable, DateExpire, LeaveDaysAvailable) VALUES (@EmployID,@DateOnJob, @DateAvailable,@DateExpire, @LeaveDaysAvailable)
FETCH NEXT FROM authors_cursor
into @EmployID,
@DateOnJob,
@DateAvailable,
@DateExpire,
@LeaveDaysAvailable
END
CLOSE authors_cursor
DEALLOCATE authors_cursor