drop procedure propageset
go

Create Procedure ProPageSet
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页(必须是唯一性的字符，比如标识种子)
@colorder varchar(50), --需要排序的字段(为空，则默认为col)
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
--@sqlout nvarchar(4000),---返回sql语句

AS
SET NOCOUNT ON
Declare @intResult Int
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @colorder is null or rtrim(@colorder)=''
set @colorder = @col
IF @condition is null or rtrim(@condition)=''
  BEGIN--没有查询条件
  SET @where1=' Where '
  SET @where2=' '
  END
ELSE
  BEGIN--有查询条件
  SET @where1=' Where ('+@condition+') AND '--本来有条件再加上此条件
  SET @where2=' Where ('+@condition+') '--原本没有条件而加上此条件
  END
SET @sql='Select @intResult=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
Select @pages=CEILING((@intResult+0.0)/@pagesize)--计算总页数
IF @orderby=0
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+@col+'>(Select MAX('+@col+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) orDER BY '+@colorder
ELSE
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+@where1+@col+'<(Select MIN('+@col+') '+ ' FROM (Select TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) orDER BY '+@colorder+' DESC'
IF @page=1--第一页
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@colorder+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--set @sqlout = @sql
EXEC(@sql)
--print 'Sql语句输出为: ' + @sqlout
-------------------------------------------------------------------------------------------------------------------------------------------
If @@Error <> 0
  Begin
  RollBack Tran
  Return -1
  End
Else
  Begin
  Commit Tran
  Return @intResult
  End
GO