分页存储过程

CREATE procedure [dbo].[Prc_splitpage]
    @sql nvarchar(4000),
@idField nvarchar(100),
    @page int=1,
    @pageSize int,
    @pageCount int=0 out,
    @recordCount int=0 out,
@where nvarchar(4000),
@order nvarchar(4000)
as
declare @sqlLen int
declare @sqlWhereIndex int
declare @sqlWhere nvarchar(4000)
declare @sqlOrderIndex int
declare @sqlOrder nvarchar(4000)
declare @sqlBracketIndex int
declare @sqlFromIndex int
declare @leftBracketNumber int
declare @rightBracketNumber int

    set @sqlWhere=''
    set @sqlOrder=''
    set @sqlWhereIndex=-1
    set @sqlOrderIndex=-1
    set @sqlFromIndex=-1

select @sql=LOWER(@sql)
    select @sqlLen=len(@sql)

while (charindex(' order by ',@sql,@sqlOrderIndex+1)>0)
   begin
  set @sqlOrderIndex=charindex(' order by ',@sql,@sqlOrderIndex+1)
   end
    set @sqlBracketIndex=@sqlOrderIndex
    set @leftBracketNumber=0
    set @rightBracketNumber=0
while (charindex('(',@sql,@sqlBracketIndex+1)>0)
   begin
        set @leftBracketNumber=@leftBracketNumber+1
  set @sqlBracketIndex=charindex('(',@sql,@sqlBracketIndex+1)
   end
    set @sqlBracketIndex=@sqlOrderIndex
while (charindex(')',@sql,@sqlBracketIndex+1)>0)
   begin
        set @rightBracketNumber=@rightBracketNumber+1
  set @sqlBracketIndex=charindex(')',@sql,@sqlBracketIndex+1)
   end
    if(@leftBracketNumber!=@rightBracketNumber) set @sqlOrderIndex=-1
    if(@sqlOrderIndex>0)
      begin
        select @sqlOrder=substring(@sql,@sqlOrderIndex,@sqlLen)
        select @sql=substring(@sql,0,@sqlOrderIndex)
      end
    else
      begin
        set @sqlOrder=@order
      end

while (charindex(' where ',@sql,@sqlWhereIndex+1)>0)
   begin
  set @sqlWhereIndex=charindex(' where ',@sql,@sqlWhereIndex+1)
   end
    set @sqlBracketIndex=@sqlWhereIndex
    set @leftBracketNumber=0
    set @rightBracketNumber=0
while (charindex('(',@sql,@sqlBracketIndex+1)>0)
   begin
        set @leftBracketNumber=@leftBracketNumber+1
  set @sqlBracketIndex=charindex('(',@sql,@sqlBracketIndex+1)
   end
    set @sqlBracketIndex=@sqlWhereIndex
while (charindex(')',@sql,@sqlBracketIndex+1)>0)
   begin
        set @rightBracketNumber=@rightBracketNumber+1
  set @sqlBracketIndex=charindex(')',@sql,@sqlBracketIndex+1)
   end
    if(@leftBracketNumber!=@rightBracketNumber) set @sqlWhereIndex=-1
    if(@sqlWhereIndex>0)
      begin
        select @sqlWhere=substring(@sql,@sqlWhereIndex,@sqlLen)
        select @sql=substring(@sql,0,@sqlWhereIndex)
        if(len(isnull(@where,'')) >0)
          begin
            select @sqlWhere=@sqlWhere+' and '+@where
          end
      end
    else
      begin
        if(len(isnull(@where,'')) >0)select @sqlWhere=' where '+@where
      end

while (charindex(' from',@sql,@sqlFromIndex+1)>0)
   begin
  set @sqlFromIndex=charindex(' from',@sql,@sqlFromIndex+1)
   end
    set @sqlBracketIndex=@sqlFromIndex
    set @leftBracketNumber=0
    set @rightBracketNumber=0
while (charindex('(',@sql,@sqlBracketIndex+1)>0)
   begin
        set @leftBracketNumber=@leftBracketNumber+1
  set @sqlBracketIndex=charindex('(',@sql,@sqlBracketIndex+1)
   end
    set @sqlBracketIndex=@sqlFromIndex
while (charindex(')',@sql,@sqlBracketIndex+1)>0)
   begin
        set @rightBracketNumber=@rightBracketNumber+1
  set @sqlBracketIndex=charindex(')',@sql,@sqlBracketIndex+1)
   end
    if(@leftBracketNumber!=@rightBracketNumber) set @sqlFromIndex=-1

    if(@idField is not null and len(@idField)>0)
       begin
        declare @fromSql nvarchar(4000)
        declare @totalSql nvarchar(4000)
  declare @totalnum int
        if(@sqlOrder is null or len(@sqlOrder)=0) select @sqlOrder=' order by '+@idField
        select @fromSql=substring(@sql,@sqlFromIndex,@sqlLen)
        select @totalSql='select @totalnum=count(1) '+@fromSql+@sqlWhere
  exec sp_executesql @totalSql,N'@totalnum int output',@totalnum output

        set @pageCount=@totalnum
  set @recordCount = @pageCount
  select @pagecount=ceiling(1.0*@pagecount/@pagesize),@page=(@page-1)*@pagesize+1

  select @sql='select A.*,B.rowNum from ('+@sql+@sqlWhere+') A,(select ROW_NUMBER() Over('+@sqlOrder+') as rowNum, '+@idField+@fromSql+@sqlWhere+') B where B.rowNum>='+cast(@page as varchar)+' and B.rowNum<'+cast((@page+@pagesize) as varchar)+' and B.'+@idField+'=A.'+@idField+' order by B.rowNum'

set nocount on

--使用游标
  declare @p3 int
  exec sp_cursoropen @p3 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
  exec sp_cursorfetch @p3,16,1,@pagesize
  exec sp_cursorclose @p3
       end
    else
       begin
  select @sql=@sql+@sqlWhere+@sqlOrder

  set nocount on
  declare @p1 int
  exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
  set @recordCount = @pageCount
  select @pagecount=ceiling(1.0*@pagecount/@pagesize),@page=(@page-1)*@pagesize+1
  exec sp_cursorfetch @p1,16,@page,@pagesize
  exec sp_cursorclose @p1
       end

调用：

DECLARE
@pageCount int,
@recordCount int

                        EXEC [Prc_splitpage]
                             @sql = N'select a.* from glunimportreceivablelistsubps(2,1,''Y'',''01/01/2011'',''10/31/2011'')a ',
                                @idField = 'mainid',
                             @page = 1,
                             @pageSize = 20,
                             @pageCount = @pageCount OUTPUT,
                             @recordCount = @recordCount OUTPUT,
                             @where = N'',
                             @order = N''

SELECT @recordCount as N'@recordCount'