SQL 存储过程的分页

最新推荐文章于 2024-09-05 18:50:50 发布

最新推荐文章于 2024-09-05 18:50:50 发布 · 54 阅读

本文介绍了一个具体的SQL存储过程示例，该过程用于查询特定用户的项目活动记录，包括正在进行和已完成的项目。通过复杂的联表查询及筛选条件，实现对项目活动的有效检索，并支持分页显示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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