alter PROCEDURE PrcPager
-- 获得某一页的数据 -- 
@currPage int = 1,                        --当前页页码 (即Top currPage) 
@showColumn nvarchar(2000) = '*',          --需要得到的字段 (即 column1,column2,......)
@tempTableColumn nvarchar(2000), 
@tabName nvarchar(2000),                   --需要查看的表名 (即 from table_name) 
@strWhere nvarchar(2000) = '',             --查询条件 (即 where condition......) 不用加where关键字 
@ascColumn nvarchar(1000) = '',            --排序的字段名 (即 order by column asc/desc) 
@bitOrderType bit = 0,  --排序的类型 (0为升序,1为降序) 
@pkColumn varchar(50) = '',  --主键名称 
@pageSize int = 20,   --分页大小 
@PageCount  int OUTPUT, --总页数
@SumCount int output    --记录总数
AS 
BEGIN -- 存储过程开始 
-- 该存储过程需要用到的几个变量 
DECLARE @strSql nvarchar(4000)           --该存储过程最后执行的语句 
declare @MaxOrMin nvarchar(100)          --取最大值或最小值的判断
DECLARE @strOrderType nvarchar(1000)     --排序类型语句 (order by column asc或者order by column desc) 
declare @sqlTemp nvarchar(1000)          --临时使用的sql语句
--计算总记录数  计算一次 页面传回值的时候就不用计算了
if @SumCount=0
begin
    if @strWhere != '' 
    begin
       SET @sqlTemp=N'SELECT @SumCount=COUNT(*)'
            +N' FROM '+@tabName
            +N' where  '+@strWhere
    end 
    else
    begin
        SET @sqlTemp=N'SELECT @SumCount=COUNT(*)'
            +N' FROM '+@tabName
    end
    EXEC sp_executesql @sqlTemp,N'@SumCount int OUTPUT',@SumCount OUTPUT
  
end  
  SET @PageCount=(@SumCount+@PageSize-1)/@PageSize
---------------------------------------------------------------------
  -- bitOrderType=1即执行降序
BEGIN 
IF @bitOrderType = 1   
BEGIN 
    SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC' 
    set @MaxOrMin =' <(select min'
END 
ELSE 
BEGIN 
    SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC' 
    set @MaxOrMin =' >(select max'
END 
-----------------------------------------------------------------
if(@strWhere!='')
    set @strWhere=' where '+@strWhere
if @currPage=0
set @currPage=1
IF @currPage = 1    -- 如果是第一页
BEGIN
        SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@tempTableColumn+' FROM '+@tabName+' '+@strWhere+' '+@strOrderType 
END 
ELSE  -- 其他页 前一半的记录if @currPage<@PageCount/2 
BEGIN 
set @strSql=N'if object_id(''tempdb..#sel'')is not null  drop table #sel
'
--exec sp_executeSql @strSql
set  @strSql=@strSql+'select identity(int,1,1) as id,* into #sel from (select TOP 100 PERCENT '+@tempTableColumn+'
from '+@tabName+@strWhere +  @strOrderType+') as newtab
'
-- exec sp_executeSql @strSql --创建临时表
--拼接查询结果代码
 SET @strSql =@strSql+'select top '+str(@pageSize)+' '+@showColumn+' 
 from #sel where id'+@MaxOrMin+'(id) as id from (select top '+str((@currPage-1)*@pageSize)+' id 
 from #sel ) as T )'
END 
END 
EXEC sp_executesql @strSql
END  -- 存储过程结束