sql2000的游标

超、凢脫俗 2022-09-18 08:52 151阅读 0赞

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][AgreeStatus_status] and [WriteID=@WriteID][WriteID_WriteID]  
         UNION  
                  select leaveid from VIEW\_GetLeaveStatistics  
                  where LeaveEndTime between @StartTime and @EndTime and [AgreeStatus=@status][AgreeStatus_status] and [WriteID=@WriteID][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][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][WriteID_WriteID]  
         UNION  
                  select leaveid from VIEW\_GetLeaveStatistics  
                  where LeaveEndTime between @StartTime and @EndTime and [WriteID=@WriteID][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][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

[AgreeStatus_status]: mailto:AgreeStatus=@status
[WriteID_WriteID]: mailto:WriteID=@WriteID
[leaveid_ID]: mailto:leaveid=@ID