sql2000的游标
来源:互联网 发布:生死时速打字游戏mac 编辑:程序博客网 时间:2024/04/28 02:19
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
GO
- sql2000的游标
- sql2000-游标的使用
- ADO操作Ms SQL2000时游标问题
- 在SQL2000中使用游标进行计算经纬度(原创)
- SQL2000里的数据类型
- SQL2000表的应用
- Sql2000数据库的使用
- SQL2000里的数据类型
- SQL2000里的数据类型
- SQL2000里的数据类型
- SQL2000的数据类型
- SQL2000里的数据类型
- SQL2000里的数据类型
- SQL2000里的数据类型
- sql2000 触发器的应用
- SQL2000 N' '的意思
- sql2000 的bcp命令
- sql2000的jdbc连接
- 教你27招,让你在社交,职场上人人对你刮目相看
- Asp.net中Split函数的应用及注意事项。
- The difference between GetDC and GetWindowDC
- Flex 2 扩展DataGrid实现行与列的自动合计
- Flash cs3 第二天
- sql2000的游标
- 评“最牛B”程序员招聘广告与“大学生搓澡工”两事件
- 系统盘垃圾清理(XP版)
- Web Service: UDDI之JUDDI配置
- 正则表达式
- 读 有感
- 今天在我的博客上挂我的一张简历!
- 第一个FLEX2项目终于见到希望,终于要结束了,有点时间写一点心得
- 望高人指点