ms-sql 存储过程分页

Create PROCEDURE [dbo].[UP_QuickSearch]
    @TblName      VARCHAR(255),       -- 表名
    @PrimaryKey   VARCHAR(20),          -- 主键
    @OrderBy      varchar(100),       --排序
    @FldNames     VARCHAR(1000),       -- 要返回的字段
    @IsCount      int ,               --1为计算总计录数
    @PageSize     INT = 10,           -- 页尺寸
    @PageIndex    INT = 1,            -- 页码
    @strWhere     VARCHAR(8000) = ''  -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL   varchar(5000),@order varchar(50)

if @OrderBy != ''
    set @order=' order by '+@OrderBy
else
    set @order=' order by '+@PrimaryKey +' desc '
if @strWhere !=''
set @strWhere=' where '+@strWhere
if @IsCount<>1
set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@order+') AS RowNumber,'+@FldNames+' FROM ['+@TblName+']'+@strWhere+') AS tempT WHERE RowNumber BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
else
set @strSQL='select count(0) as Total from ['+@TblName+']'+@strWhere
exec (@strSQL)
GO

Select * FROM (
    select ROW_NUMBER() Over(order by 排序字段 desc) as rowId,* from 数据表
)
where rowId between 页面大小*(页号-1) and 页面大小*页号
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description:    <高效分页存储过程，仅适用于Sql2005>
-- Notes:        <排序字段强烈建议建索引>
-- Page2005 'dyj_vclient','client_id,last_dt','client_id','last_dt','desc','',20,1,0
-- =============================================
ALTER Procedure [dbo].[Page2005]
@TableName varchar(50),          --表名
@Fields varchar(5000) = '*',      --字段名(全部字段为*)
@PrimaryKey varchar(20),          --主键
@OrderField varchar(5000),        --排序字段(必须!支持多字段)
@Orderby varchar(8)='desc',      --排序方式(desc：倒序，asc：顺序)
@sqlWhere varchar(5000) = Null,  --条件语句(不用加where)
@pageSize int,                    --每页多少条记录
@pageIndex int = 1 ,              --指定当前为第几页
@TotalPage int output            --返回总页数
as
begin
    Begin Tran --开始事务
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;   
    if isnull(@PrimaryKey,'')=''
      set @PrimaryKey='*'
    --计算总记录数
       
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count('+ @PrimaryKey +') from ' + @TableName
    else
        set @sql = 'select @totalRecord = count('+ @PrimaryKey +') from ' + @TableName + ' where ' + @sqlWhere
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       
   
    --计算总页数
    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ' '+ @Orderby +') as rowId,' + @Fields + ' from ' + @TableName
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ' '+ @Orderby +') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere   
       
   
    --处理页数超出范围情况
    if @PageIndex <=0
        Set @pageIndex = 1
   
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage
    --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
   
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1
    --继续合成sql语句
    set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
   
    Exec(@Sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
    Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
      End   
end