MS SQL SERVER 分页通用存储过程

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