sql 语句分页

create procedure SqlDataPaging
    @tbName varchar(255), --表名
    @tbFields varchar(1000), --返回字段
    @OrderField varchar(255), --排序的字段名
    @PageSize int, --页尺寸
    @PageIndex int, --页码
    @OrderType bit, --排序类型,1是升序,0是降序
    @strWhere varchar(1500), --查询条件
    @Total int output --返回总记录数
as
    declare @strSql varchar(5000) --主语句
    declare @strOrder varchar(200) --排序
    declare @strSqlCount nvarchar(500)--查询记录总数主语句
    declare @strtemp varchar(50) --排序附加语句
    --------------排序-----------------1是降序,0未升序
if @OrderType !=0
begin
    set @strtemp= '>(select max(' set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
    set @strtemp= '<(select min(' set @strOrder='order by '+ @OrderField + ' desc'
end
--------------总记录数---------------
if @strWhere !=''
begin
    set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + 'where '+ @strWhere
end
else
begin
    set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName
end --------------如果是第一页------------
if @PageIndex =1
begin
if @strWhere !=''
begin
    set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName +' where ' + @strWhere + ' ' + @strOrder
end
else
begin
    set @strSql = 'Select top' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbName + ' ' + @strOrder
end
end
else
begin
    if @strWhere !=''
    begin
        set @strSql='Select top ' +str(@PageSize) + ' ' + @tbFields +' from ' + @tbName + '
            where '+@OrderField +' ' + @strtemp+ ' ' +@OrderField+ ') from (select top' +
                str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbName +
                    @strWhere+ ' '+ @strOrder +') as tb)'+ @strOrder
    end
    else
    begin
        set @strSql='Select top ' + str(@PageSize) + ' ' + @tbFields +' from ' +
            @tbName + ' where '+@OrderField + ' ' + @strtemp+ ' ' +
            @OrderField+ ') from (select top' + str((@PageIndex-1)*@PageSize) + ' '+
                @OrderField +' from '+@tbName + ' '+ @strOrder +') as tb)'+ @strOrder
    end
end
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output exec(@strSql)