CREATE proc GetThePage
@pagesize int,
@pageindex int,
@col nvarchar(100),
@tb nvarchar(20),
@wherestr nvarchar(100),
@sOrder varchar(10),
@sOrdStr nvarchar(20)
AS
--@pagesize 分页设置大小
--@pageindex 当前页面索引值
--@col 列名 'a,b,c...'
--@tb 表名
--@wherestr 'where 1=1 and .....'条件语句
--@sOrder //'asc' 或者 'desc'
--@sOrdStr 排序字段
DECLARE @sSQL NVARCHAR(4000)
if @sOrder = 'asc'
set @sSQL = 'select '+@col+' from (select top ' + cast(@pagesize as nvarchar) +' '+@col+' from ( select top '+cast((@pagesize * @pageindex + @pagesize) as nvarchar)+' '+@col+' from '+ @tb +' '+ @wherestr +' order by '+ @sOrdStr +' asc ) tt '+ @wherestr +' order by '+ @sOrdStr +' desc) tp '+@wherestr+' order by '+ @sOrdStr +' asc'
else
set @sSQL = 'select '+@col+' from (select top ' + cast(@pagesize as nvarchar) +' '+@col+' from ( select top '+cast((@pagesize * @pageindex + @pagesize) as nvarchar)+' '+@col+' from '+ @tb + ' ' + @wherestr + ' order by '+ @sOrdStr +' desc ) tt '+ @wherestr +' order by '+ @sOrdStr +' asc) tp '+@wherestr+' order by '+ @sOrdStr +' desc'
EXEC(@sSQL)
go