游标练习

 

USE [MHinfo]
GO
/****** Object: StoredProcedure [dbo].[U_AttMettingPerson] Script Date: 03/25/2015 23:46:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[U_AttMettingPerson]
@id int
as
declare

@UserName varchar(64),
@UserID varchar(32),
@DeptID char(4),
@ArriveTime datetime ;

begin
declare ATT_Meeting_Peron_sursor cursor for

Select A.MeetingID as MeetingID,
D.UserName As UserName,
C.UserID as UserID,
D.DeptID as DeptID ,
Convert(varchar(100),
( Select Top 1 AttDateTime
From T_AttDataInfo
Where CardNo = D.LogonID And AttDateTime >= DateAdd(Minute,-20,A.BeginDate) And AttDateTime <= DateAdd(Minute,20,A.BeginDate) ) ,120 ) As ArriveTime From T_Meeting A, T_MeetingAddress B, T_JoinMeetingPerson C, T_UserBaseInfo D Where A.MeetingAddressID = B.MeetingAddressID And A.MeetingID = C.MeetingID And C.UserID = D.UserID And A.MeetingID =@id

open ATT_Meeting_Peron_sursor

fetch next from ATT_Meeting_Peron_sursor into @id,@UserName ,@UserID,@DeptID,@ArriveTime;
while @@FETCH_STATUS=0
begin
update T_JoinMeetingPerson set @ArriveTime=BeginDate=AA.ArriveTime from
( Select A.MeetingID,D.UserID, Convert(varchar(100), ( Select Top 1 AttDateTime From T_AttDataInfo Where CardNo = D.LogonID And AttDateTime >= DateAdd(Minute,-20,A.BeginDate) And AttDateTime <= DateAdd(Minute,20,A.BeginDate) ) ,120 ) As ArriveTime From T_Meeting A, T_MeetingAddress B, T_JoinMeetingPerson C,T_UserBaseInfo D Where A.MeetingAddressID = B.MeetingAddressID And A.MeetingID = C.MeetingID And C.UserID = D.UserID
) as AA
where AA.MeetingID=T_JoinMeetingPerson.MeetingID and AA.UserID=T_JoinMeetingPerson.UserID and AA.MeetingID=@id ;

fetch next from ATT_Meeting_Peron_sursor into @id,@UserName,@UserID,@DeptID,@ArriveTime;
end;
begin
Select A.MeetingID as MeetingID, D.UserName As UserName,C.UserID as UserID,D.DeptID as DeptID , Convert(varchar(100), ( Select Top 1 AttDateTime From T_AttDataInfo Where CardNo = D.LogonID And AttDateTime >= DateAdd(Minute,-20,A.BeginDate) And AttDateTime <= DateAdd(Minute,20,A.BeginDate) ) ,120 ) As ArriveTime From T_Meeting A, T_MeetingAddress B, T_JoinMeetingPerson C, T_UserBaseInfo D Where A.MeetingAddressID = B.MeetingAddressID And A.MeetingID = C.MeetingID And C.UserID = D.UserID And A.MeetingID =@id;
fetch next from ATT_Meeting_Peron_sursor into @id,@UserName ,@UserID,@DeptID,@ArriveTime;

end ;
close ATT_Meeting_Peron_sursor ;
deallocate ATT_Meeting_Peron_sursor;
end;