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