一个高效的分页存储过程

摘自优快云

CREATE PROCEDURE PAGINATION

@tblName   varchar(255),      

@strGetFields varchar(1000) = '*', 

@fldName varchar(255)='',    

@PageSize   int = 10,      

@PageIndex  int = 1,      

@doCount  bit = 0, 

@OrderType bit = 0, 

@strWhere  varchar(1500) = '' 

AS

declare @strSQL   varchar(5000)     

declare @strTmp   varchar(110)      

declare @strOrder varchar(400)      

if @doCount != 0

  begin

    if @strWhere !=''

    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere

    else

    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

end 

else

begin

 

if @OrderType != 0

begin

    set @strTmp = '<(select min'

    set @strOrder = ' order by [' + @fldName +'] desc'

end

else

begin

    set @strTmp = '>(select max'

    set @strOrder = ' order by [' + @fldName +'] asc'

end

 

if @PageIndex = 1

begin

    if @strWhere != ''  

    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder

     else

     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName + '] '+ @strOrder

end

else

begin

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['

    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder

 

if @strWhere != ''

    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['

        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '

        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

end

end  

exec (@strSQL)

GO