分页过程

Alter Procedure Page
 @Table varchar(50) = 'Orders', --表名
 @Fields varchar(500) = 'OrderID,CustomerID,Freight,ShipName,ShipAddress,OrderDate',--列
 @OrderField varchar(50) = 'OrderID',--排序字段名称
 @OrderType bit = 1,--排序方式,非0则降序
 @DoCount bit = 1,--是否统计记录总数
 @PageSize int = 10,-- 页大小
 @CurrentPage int = 1 -- 页码

As
 set nocount on
 declare @where varchar(500)--查询条件
 declare @Sql varchar(5000)-- 主代码
 declare @OrderStr varchar(100)
 declare @Temp varchar(1000) -- 临时变量
 
 set @where = 'OrderID=OrderID'

if @OrderType != 0--如果非0则按降序排序
   begin
   set @OrderStr = ' order by '+@OrderField+' desc'
   set @Temp = '<(select min'
   end
else--为0则按升序排序
   begin
   set @OrderStr = ' order by '+@OrderField+' asc'
   set @Temp = '>(select max'
   end

if @DoCount != 0--统计记录总数
       set @Sql = 'select count(*) from '+@Table+' where '+@where
else
 if @CurrentPage = 1
   begin
    if @where != ''
       set @where = ' where '+@where
    set @Sql = 'select top '+LTrim(str(@PageSize))+' '+@Fields+' from '+@Table+@where+@OrderStr
   end
 else
   begin
     if @where != ''
              begin
       set @Sql = 'select top '+LTrim(str(@PageSize))+' '+@Fields+' from '+@Table+'
 where '+@OrderField+@Temp+'('+@OrderField+') from (select top '+LTrim(str((@CurrentPage-1)*@PageSize))+' '+@OrderField+' from '+@Table+' where '+@where+@OrderStr+') as t)
    and '+@where+@OrderStr
              end
            else
              begin
              set @Sql = 'select top '+LTrim(str(@PageSize))+' '+@Fields+' from '+@Table+'
 where '+@OrderField+@Temp+'('+@OrderField+') from (select top '+LTrim(str((@CurrentPage-1)*@PageSize))+' '+@OrderField+' from '+@Table+@OrderStr+') as t)
    '+@OrderStr
              end
   end
exec(@Sql)
Go