sql2005存储过程调用游标及分页

--删除语句
--drop proc proc_proc
--创建
create proc proc_proc
--传入参数
@Flag int,
@UserID nvarchar(50),
@BeginTime datetime,
@EndTime datetime,
@PageIndex int,
@PageSize int
as
--创建临时表
create table #tmp(
id uniqueidentifier,
UserAccount nvarchar(50),
Content nvarchar(255),
ScheduledTime datetime,
CreatedTime datetime,
Status int,
SubmitCount int,
Flag int
)
--传出参数
declare @TotalCount int
--声明游标
declare my_cursor cursor scroll
for
select distinct BatchID from SmsSubmit
where UserAccount= @UserID
and CreatedTime<(select DATEADD ( dd , 1, @EndTime )) and CreatedTime>(select DATEADD ( dd , -1, @BeginTime ))
and Flag=@Flag
open my_cursor
declare @BatchID nvarchar(50)
fetch next from my_cursor into @BatchID
while(@@fetch_status=0)
begin
  --插入临时表
  insert into #tmp
  select TOP 1 id,UserAccount,Content,ScheduledTime,CreatedTime,Status,SubmitCount,Flag from SmsSubmit where BatchID=@BatchID ORDER by CreatedTime desc
fetch next from my_cursor into @BatchID
end
close my_cursor
deallocate my_cursor--删除游标
select @TotalCount=count(*) from #tmp
--分页
select * from(select *,row_number() over(order by CreatedTime) as rownum from #tmp) as a
where rownum BETWEEN (@PageIndex-1)*(@PageSize+1 )
AND @PageIndex*@PageSize order by CreatedTime
--删除临时表
drop table #tmp
return @TotalCount
--创建完毕
--调运存储过程
exec proc_proc1,&#39;zjh123&#39;,&#39;2008-1-29 0:00:00&#39;,&#39;2008-3-3 0:00:00&#39;,1,10
存储过程创建的背景:源于一个表中相同的BatchID却对应有多条不同的表记录,但只想取出 where条件后的不同用户的一条BatchID