比较通用的存储过程分页 需要改进之处大家多多指点!

 
--名称：创建分页存储过程 
--时间：2011-07-04
--作者：wuhan
alter proc proc_wuhan_getpages  --创建存储过程
@pageSize int,      --页大小
@currentPage int,     --当前页（第几页） 
@tableName nvarchar(100),   --表名
@columns  nvarchar(1000),   --字段名
@condition nvarchar(100),   --查询条件
@orderType int,      --排序类型（0为升序，1为降序）
@orderColumn nvarchar(100),   --排序字段
@premaryKeyColumn nvarchar(100)  --主键字段名称
as
begin
 declare @strSqlStatement nvarchar(2000)   --存储过程最后执行的语句
 declare @chartype nvarchar(10)
 begin
  if @condition=''       --查询条件为空  
  begin
   if @orderType=0       --升序
   begin
    set @chartype=' asc'
    set @strSqlStatement = 'select top ' +ltrim(@pageSize) +' '+@columns +' from ' + @tableName+ ' where ('
       + @premaryKeyColumn+' not in (select Top '+ convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn
       + ' from '+ @tableName + ' order by ' +@orderColumn + @chartype  +')) order by ' +@orderColumn+ @chartype
   end
   else if @orderType=1
   begin
    set @chartype=' desc'    --降序
    set @strSqlStatement = 'select top ' +ltrim(@pageSize) +' '+@columns +' from ' + @tableName+ ' where ('
       + @premaryKeyColumn+' not in (select Top '+ convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn
       + ' from '+ @tableName + ' order by ' +@orderColumn + @chartype  +')) order by ' +@orderColumn+ @chartype
   end
   print @strSqlStatement
   exec (@strSqlStatement)     --输出最后要执行的语句
  end
  else if @condition!=''        --查询条件不为空
   begin
    if @orderType=0       --升序
    begin
     set @chartype=' asc'
     set @strSqlStatement = 'select top ' +ltrim(@pageSize) +' '+@columns +' from ' + @tableName+ ' where ('
        + @premaryKeyColumn+' not in (select Top '+ convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn
        + ' from '+ @tableName + ' order by ' +@orderColumn + @chartype  +')and '+ @condition +') order by ' +@orderColumn+ @chartype
    end
    else if @orderType=1     
    begin
     set @chartype=' desc'    --降序
     set @strSqlStatement = 'select top ' +ltrim(@pageSize) +' '+@columns +' from ' + @tableName+ ' where ('
        + @premaryKeyColumn+' not in (select Top '+ convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn
        + ' from '+ @tableName + ' order by ' +@orderColumn + @chartype  +')and '+ @condition +')order by ' +@orderColumn+ @chartype
    end
    print @strSqlStatement
    exec (@strSqlStatement)     --输出最后要执行的语句
   end
  else
   print '出错了吧，还是要多多练习的!'+ ltrim(@pageSize)
 end
end