MS SQL SERVER 分页通用存储过程
set ANSI_NULLS ONset QUOTED_IDENTIFIER ON
go
-- Procedure
/*=============================================
过程名称：
功能描述:分页通用存储过程，用来获取记录集的行数、页码数
参数说明：
调试记录：
declare @RowCount int ,
@PageCount int
exec 'select * from S_GRID',12,@RowCount output,@PageCountoutput
select @RowCount,@PageCount
=============================================
修改历史：
修 改 人：
修改日期：
修改说明：
'select a.id,a.usercode,a.content,
a.cudate,a.state,b.collname,
c.username,a.collcode
from m_call_help as a,m_collecter as b,p_user as c
where a.collcode=b.collcode
and a.usercode = c.usercode
and sendtype = 1
and left(gridcode,len(321102)) = 321102','15','',''
=============================================*/
ALTER PROCEDURE .
(
@Query nvarchar(4000),--SQL语句
@PageSize int,--每页大小
@RowCount int output,--返回值：行数
@PageCount int output --返回值：总页数
)
AS
declare @SqlString nvarchar(4000)
declare @ParmDefinition nvarchar(100)
set @SqlString=N'select @num=count(1) from ('+@Query+') as tt'
set @ParmDefinition=N'@num int output'
exec sp_executesql
@SqlString,
@ParmDefinition,
@num=@RowCount output
SET @PageCount = 0
IF @RowCount>0
BEGIN
IF @PageSize<=0
set @PageCount = 1
ELSE
set @PageCount = CEILING(cast(@RowCount as float)/cast(@PageSize as float))
END
页:
[1]