SQL 存储过程的分页

create procedure
--ty_exchange_search_my_projects @usercode=1500
--select * from t_user
[dbo].[ty_exchange_search_my_projects_shark]
(
@pmiUserFid int ,
@dateBegin datetime = '2005-01-01',
@dateEnd datetime = '3000-01-01',
@page_index int = 0 ,
@page_size int = 15
)
as
begin
 set @dateBegin = cast(convert(varchar(100),@dateBegin,23) + ' 00:00:00' as datetime)
 set @dateEnd = cast(convert(varchar(100),@dateEnd,23) + ' 23:59:59' as datetime)
 SET NOCOUNT ON;
    SET   ANSI_NULLS   OFF

 DECLARE @s INT SET @s = @page_index * @page_size + 1
 DECLARE @e INT SET @e = @s + @page_size
      
    select * from
   (
  SELECT c_code, c_title,c_event_desc, ROW_NUMBER() OVER ( ORDER BY c_fid ASC) AS rn
     from
 (
     SELECT a.c_code, a.c_title,a.c_event_desc,a.c_fid
  FROM dbo.syn_tu_event a inner join syn_ty_wf_ex_local_act_inst b  on a.c_wf_inst_id=b.workflow_instance_id
  WHERE b.in_date between @dateBegin and  @dateEnd and  b.receive_type_id = 100602 AND b.receive_object_id in
  (
    SELECT r.role_id FROM  syn_ty_pmi_user AS u INNER JOIN  syn_t_userRole AS r ON u.user_id = r.user_id WHERE (u.fid = @pmiUserFid)
  )
        union all
        SELECT a.c_code, a.c_title,a.c_event_desc,a.c_fid
  FROM dbo.syn_tu_event_finish a inner join syn_ty_wf_ex_local_act_inst b  on a.c_wf_inst_id=b.workflow_instance_id
  WHERE b.in_date between @dateBegin and  @dateEnd and  b.receive_type_id = 100602 AND b.receive_object_id in
  (
    SELECT r.role_id FROM  syn_ty_pmi_user AS u INNER JOIN  syn_t_userRole AS r ON u.user_id = r.user_id WHERE (u.fid = @pmiUserFid)
  )

 ) b
  ) FK where rn >= @s and rn < @e

 select count(1) as iCount from
 (
        SELECT a.c_code, a.c_title,a.c_event_desc
  FROM dbo.syn_tu_event a inner join syn_ty_wf_ex_local_act_inst b  on a.c_wf_inst_id=b.workflow_instance_id
  WHERE b.receive_type_id = 100602 AND b.receive_object_id in
  (
    SELECT r.role_id FROM  syn_ty_pmi_user AS u INNER JOIN  syn_t_userRole AS r ON u.user_id = r.user_id WHERE (u.fid = @pmiUserFid)
  )
        union all
        SELECT a.c_code, a.c_title,a.c_event_desc
  FROM dbo.syn_tu_event a inner join syn_ty_wf_ex_local_act_inst b  on a.c_wf_inst_id=b.workflow_instance_id
  WHERE b.receive_type_id = 100602 AND b.receive_object_id in
  (
    SELECT r.role_id FROM  syn_ty_pmi_user AS u INNER JOIN  syn_t_userRole AS r ON u.user_id = r.user_id WHERE (u.fid = @pmiUserFid)
  )
 ) a

end