sql2000的游标_sql2000 游标-CSDN博客

本文链接：https://blog.csdn.net/yeness/article/details/1573641

CREATE PROCEDURE dbo.Proc_GetLeaveStatistics
(
   @StartTime datetime,
   @EndTime datetime,
   @status varchar(256)
)
AS
CREATE TABLE [dbo].[#LEAVE_temp] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (256) null,
        [LeaveScope] [varchar](256) null,
[CountLeaveHour] [varchar](256) NULL ,
[VerificationName] [varchar](256) NULL ,
[AgreeScope] [varchar](256) NULL ,
[VerificationCountTime] [varchar](256) NULL ,
[StatusExplain] [varchar](256) NULL

)
DECLARE @WriteID varchar(50),@UserName varchar(50),@LeaveScope varchar(128),@LeaveStartTime varchar(128),@LeaveEndTime varchar(128)
,@CountLeaveHour varchar(128),
@VerificationName varchar(128),@ID VARCHAR(50),
@AgreeScope varchar(128),@VerificationCountTime varchar(128),@StatusExplain varchar(128)
DECLARE LEAVE_CURSOR CURSOR FOR
SELECT WriteID FROM ask_leave GROUP BY WriteID
OPEN LEAVE_CURSOR

FETCH FROM LEAVE_CURSOR INTO @WriteID
WHILE @@FETCH_STATUS=0
BEGIN
if @status ='1'
begin
DECLARE LEAVE_CURSOR1 CURSOR FOR
     select LEAVEID AS ID from VIEW_GetLeaveStatistics where leaveid in ( select leaveid from VIEW_GetLeaveStatistics
                  where LeaveStartTime between @StartTime and @EndTime and AgreeStatus=@status and WriteID=@WriteID
         UNION
                  select leaveid from VIEW_GetLeaveStatistics
                  where LeaveEndTime between @StartTime and @EndTime and AgreeStatus=@status and WriteID=@WriteID
                        )
OPEN LEAVE_CURSOR1
FETCH FROM LEAVE_CURSOR1 INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
select @UserName=UserName,@LeaveScope=convert(varchar(16),LeaveStartTime,20)+'-'+convert(varchar(16),LeaveEndTime,20),@CountLeaveHour=CountLeaveHour,
                  @VerificationName=VerifationName,@AgreeScope=convert(varchar(16),AgreeStartTime,20)+'-'+convert(varchar(16), AgreeEndTime,20),
                  @VerificationCountTime=VerificationCountTime,@StatusExplain=StatusExplain from VIEW_GetLeaveStatistics where leaveid=@ID

    INSERT #LEAVE_temp(UserName,LeaveScope,CountLeaveHour,VerificationName,AgreeScope,VerificationCountTime,StatusExplain)
    VALUES (@UserName,@LeaveScope,@CountLeaveHour,@VerificationName,@AgreeScope,@VerificationCountTime,@StatusExplain)
    FETCH NEXT FROM LEAVE_CURSOR1 INTO @ID
END
    CLOSE LEAVE_CURSOR1
    DEALLOCATE LEAVE_CURSOR1

end
else
begin
    DECLARE LEAVE_CURSOR2 CURSOR FOR
    select LEAVEID AS ID from VIEW_GetLeaveStatistics where leaveid in(select leaveid from VIEW_GetLeaveStatistics
                  where LeaveStartTime between @StartTime and @EndTime and WriteID=@WriteID
         UNION
                  select leaveid from VIEW_GetLeaveStatistics
                  where LeaveEndTime between @StartTime and @EndTime and WriteID=@WriteID
                        )
      OPEN LEAVE_CURSOR2
    FETCH FROM LEAVE_CURSOR2 INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
     select @UserName=UserName,@LeaveScope=convert(varchar(16) ,LeaveStartTime,20)+'-'+convert(varchar(16) ,LeaveEndTime,20),@CountLeaveHour=CountLeaveHour,
                  @VerificationName=VerifationName,@AgreeScope=convert(varchar(16),AgreeStartTime,20)+'-'+convert(varchar(16), AgreeEndTime,20),
                  @VerificationCountTime=VerificationCountTime,@StatusExplain=StatusExplain from VIEW_GetLeaveStatistics where leaveid=@ID

      INSERT #LEAVE_temp(UserName,LeaveScope,CountLeaveHour,VerificationName,AgreeScope,VerificationCountTime,StatusExplain)
      VALUES (@UserName,@LeaveScope,@CountLeaveHour,@VerificationName,@AgreeScope,@VerificationCountTime,@StatusExplain)
      FETCH NEXT FROM LEAVE_CURSOR2 INTO @ID
END
      CLOSE LEAVE_CURSOR2
      DEALLOCATE LEAVE_CURSOR2
end
   FETCH NEXT FROM LEAVE_CURSOR INTO @WriteID
END
    CLOSE LEAVE_CURSOR
    DEALLOCATE LEAVE_CURSOR
SELECT * FROM #LEAVE_temp