比較通用的預存程序分頁 需要改進之處大家多多指點!

 
--名稱：建立分頁預存程序 
--時間：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