MS SQL SERVER 分页通用存储过程

mrbear

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- Procedure
/*=============================================
过程名称： [pr_sys_CutPage]
功能描述:  分页通用存储过程，用来获取记录集的行数、页码数
参数说明：
调试记录：
declare @RowCount int ,        
@PageCount int        
exec [pr_sys_CutPage] 'select * from S_GRID',12,@RowCount output,@PageCount  output        
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   [dbo].[pr_sys_CutPage]
(
@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