SERVET分页的储存过程

use MySchool
go
--检查是否存在储存过程PROC_PAGER，存在则删除
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME='PROC_PAGER')
 DROP PROCEDURE PROC_PAGER
go
--创建储存过程PROC_PAGER
CREATE PROCEDURE PROC_PAGER
 @tableName nvarchar(64),   --表名
 @pkey nvarchar(32),--主键
 @PageCount bigint output,--总页数
 @Pages bigint output, --当前页
 @condition nvarchar(256)='1=1', --分页条件(比如:Name='Jack' and Age=18)
 @orderField nvarchar(128)=@pKey, --排序字段(比如:SID,Name)
 @pageSize int = 10,   --页大小
 @pageIndex int = 1   --页号
as
 BEGIN
 declare @SQLPAGER NVARCHAR(4000)
 set @SQLPAGER = 'SELECT TOP {PAGE_SIZE} * FROM {TABLE_NAME}
     WHERE {PKEY} NOT IN
     (
      SELECT TOP {PASS_COUNT} {PKEY}
      FROM {TABLE_NAME}
      WHERE {CONDITION}
      ORDER BY {ORDER_FIELD}
     )
     AND {CONDITION}
     ORDER BY {ORDER_FIELD}'

--此处开始替换@SQLPAGER变量中的参数
select @SQLPAGER = replace(@SQLPAGER,'{TABLE_NAME}',@tableName)
select @SQLPAGER = replace(@SQLPAGER,'{PKEY}',@pKey)
select @SQLPAGER = replace(@SQLPAGER,'{PASS_COUNT}',@PageSize)
select @SQLPAGER = replace(@SQLPAGER,'{ORDER_FIELD}',@orderField)
select @SQLPAGER = replace(@SQLPAGER,'{PAGE_SIZE}',@PageSize)
select @SQLPAGER = replace(@SQLPAGER,'{CONDITION}',@Condition)

--计算总页数
declare @sql nvarchar(4000)
set @sql = N'select @cnt=count(*) from {TABLE_NAME} where {CONDITION}'
select @SQL = replace(@SQL,'{TABLE_NAME}',@tableName)
select @SQL = replace(@SQL,'{CONDITION}',@Condition)

exec sp_executesql @sql,N'@cnt bigint output',@PageCount output
set @PageCount = (@PageCount-1)/@pageSize + 1
--select @PageCount
set @pages = @pageIndex
 
 --PRINT @SQLPAGER     --显示拼接的分页SQL
 EXEC SP_EXECUTESQL @SQLPAGER --动态执行拼接的分页SQL语句
END
go

declare @pages int,@pageIndex int
EXEC PROC_PAGER STUDENT,STUDENTNO,@pages output,@pageIndex output,@pageSize=3
print '总页数为:' + convert(nvarchar(30),@pages)
print  '当前页数为:' + convert(nvarchar(30),@pageIndex)