create PROCEDURE [dbo].[pr_com_QuerySQLPaged]
@Query nvarchar(MAX), --SQL语句
@PageSize int, --每页大小
@CurrentPage int , --当前页码
@Field nvarchar(40)='', --排序字段
@Order nvarchar(10) = 'asc ' --排序顺序
AS
declare @PageCount int,
@TempSize int,
@TempNum int,
@strSQL varchar(max),
@strField varchar(40),
@strFielddesc varchar(40),
@Tempindex int
--0,1都做第一页处理
if (@currentPage = 0)
set @currentPage = 1
set @TempNum = @CurrentPage * @PageSize
set @strField = ''
set @strFielddesc = ''
--计算总页数
declare @strCountSQL nvarchar(MAX)
set @strCountSQL = 'SELECT @total=COUNT(1) FROM (' + @Query + ')T'
--总记录数
DECLARE @rowsCount int
DECLARE @params nvarchar(500)
SET @params = '@total int OUTPUT'
EXEC sp_executesql @strCountSQL, @params, @total=@rowsCount OUTPUT
--根据总记录数,计算页数
SET @PageCount = ceiling(convert(float, @rowsCount) / convert(float, @PageSize))
--超过最后一页,显示尾页
if(@CurrentPage>=@PageCount)
set @TempSize=@rowsCount-(@PageCount-1)*@PageSize
else
set @TempSize=@PageSize
SET @Tempindex=Charindex('projcode',@Query,0)
if( @Tempindex>0 and @Tempindex<Charindex('from',@Query,0))
begin
if(@Field<>'' and @Field<>'projcode')
begin
set @strField = ',projcode ';
set @strFielddesc =',projcode desc ';
end
end
--分页SQL
if(@Order='desc')
begin
set @strSQL = '
select *
from (
select top '+CONVERT(varchar(10),@TempSize)+' *
from (
select top '+CONVERT(varchar(10),@TempNum)+' *
from ('+@Query+') as t0
order by '+@Field+' desc '+@strField+'
) as t1
order by '+@Field+@strFielddesc+'
) as t2
order by '+@Field+' desc' +@strField
end
else
begin
set @strSQL = '
select *
from (
select top '+CONVERT(varchar(10),@TempSize)+' *
from (
select top '+ CONVERT(varchar(10), @TempNum ) + ' *
from ('+@Query+') as t0
order by '+@Field+' asc '+@strField +'
) as t1
order by '+@Field+' desc '+@strFielddesc+'
) as t2
order by '+@Field +@strField
end
exec(@strSQL)
GO