创建分页的存储过程语句

只针对一张表的情况

create  procedure pro_createPagination  --创建分页的存储过程语句
@primaryKeyName varchar(20),               --主键字段名
@orderField varchar(20),                           --排序字段名
@orderFiledType varchar(10),                   --排序字段排列顺序
@tableName varchar(20),                          --表名
@fieldStr varchar(1000),                            --所须的字段名
@type bit,                                                     --类型(生成那种分页语句)                       
@strSql varchar(1000) output                    --生成的SQL语句
as
    begin
           if  @type=1

     set @strSql=' select top pageSize ' +@fieldStr+' from '
     +@tableName+' where '+@primaryKeyName+' not in
     ( select top ((pageSize*(page-1)) '+@primaryKeyName+' from '+
     @tableName +' order by '+@orderField+' '+ @orderFiledType
     + ' ) order by '+@orderField+' '+@orderFiledType
 else
       if  @orderFiledType='asc'
                 set @strSql='select top pageSize '+@fieldStr+' from '
                 +@tableName+' where '+@primaryKeyName+'>( select max
                 ('+@primaryKeyName+') from (select top
                 ((pageSize*(page-1)) '+ @primaryKeyName+
                ' from '+ @tableName+' order by '+ @orderField +' ) as T)
                 order by '+@orderField+ ''
      else
                  set @strSql='select top pageSize '+@fieldStr+' from '
                  +@tableName+' where '+@primaryKeyName+'<( select min
                  ('+@primaryKeyName+') from (select top
                  ((pageSize*(page-1)) '+ @primaryKeyName+
                  ' from '+ @tableName+' order by '+ @orderField +' desc ) as T)
                  order by '+@orderField+ ' desc

end

测试语句:

declare @strSql varchar(1000)
exec pro_createPagination 'id','id','desc','students','*',1,@strSql output
print @strSql