SQL数据分页

-- 查询满足条件的记录集,并获取指定页的数据
CREATE                   PROCEDURE Proc_QueryPages

@Fieldstr NVARCHAR ( 255 ) = ' * ' , -- 查询字段
@Tnamestr NVARCHAR ( 255 ) = '' , -- 查询表名
@Wherestr NVARCHAR ( 255 ) = '' , -- 查询条件
@Orderby   Nvarchar ( 255 ) = ' order by id desc ' , -- 查询的排序
@PageSize int = 50 ,          -- 页尺寸
@PageIndex int = 1 ,           -- 页码
@mode int = 1 ,                -- 返回模式1为数据集,0为数据总数和总页数
@Pagemode int = 1 ,         -- 是否分页,1为分页,0表示不分页
@CountRs int = 1 output,         -- --查询到的记录数
@Countpages int = 1 output             -- --查询结果总页数

AS
declare @SQLstr Nvarchar ( 4000 )
declare @Wheretemp1 Nvarchar ( 1000 )
declare @Orderfield1 Nvarchar ( 1000 )
declare @OrderTmp1 Nvarchar ( 1000 )
declare @Orderby1 Nvarchar ( 1000 )

if @mode != 0     -- 判断结果是统计记录数还是返回数据集,非零返回数据集
BEGIN
  if @Pagemode = 1 -- "1"为分页
    begin
    if @PageIndex = 1
        if @Wherestr = ''
            set @SQLstr = ' select top ' + ltrim ( str ( @PageIndex * @PageSize )) + ' ' + @Fieldstr + ' from ' + @Tnamestr + ' ' + @Orderby
        else
                  set @SQLstr = ' select top ' + ltrim ( str ( @PageIndex * @PageSize )) + ' ' + @Fieldstr + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby
        -- 以上代码是查询第一页的,这样可以加速查询
    else
        IF charindex ( ' , ' , @Orderby ) > 0
            Begin
                set @SQLstr = ' SELECT TOP ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
                    + ' FROM ' + @Tnamestr + ' WHERE (ID NOT IN (SELECT id FROM (SELECT top ' + ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
                    + ' * FROM ' + @Tnamestr + @Orderby + ' ) AS t)) ' + @Orderby
            End
        ELSE
            Begin
                set @Orderfield1 = replace ( replace ( replace ( @Orderby , ' order by ' , '' ), ' desc ' , '' ), ' asc ' , '' )
                -- --设置排序的条件字符串
                if charindex ( ' desc ' , @Orderby ) > 0
                    set @Wheretemp1 = ' where ' + @Orderfield1 + ' <(select min( ' + @Orderfield1 + ' ) from (select top '
                else
                    set @Wheretemp1 = ' where ' + @Orderfield1 + ' >(select max( ' + @Orderfield1 + ' ) from (select top '
                if @Wherestr = ''
                    set @SQLstr = ' select top ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
                    + ' from ' + @Tnamestr + ' ' + @wheretemp1
                    + ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
                    + ' ' + @Orderfield1 + ' from ' + @Tnamestr + ' ' + @Orderby + ' ) as T ) ' + @Orderby
                ELSE
                    set @SQLstr = ' select top ' + ltrim ( str ( @PageSize )) + ' ' + @Fieldstr
                    + ' from ' + @Tnamestr + ' ' + @wheretemp1
                    + ltrim ( str (( @PageIndex - 1 ) * @PageSize ))
                    + ' ' + @Orderfield1 + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby + ' ) as T ) AND '
                    + @Wherestr + ' ' + @Orderby
            End
          end
  else   -- 不分页模式代码开始
      begin
    if @Wherestr = ''
        set @SQLstr = ' select ' + @Fieldstr + ' from ' + @Tnamestr + ' ' + @Orderby
    else
          set @SQLstr = ' select ' + @Fieldstr + ' from ' + @Tnamestr + ' where ' + @Wherestr + ' ' + @Orderby
      end

  exec ( @SQLstr ) -- 执行查询
-- print @SQLstr

  END
else     -- 返回记录数和总页数代码开始
  BEGIN
    if @Wherestr = ''
        set @SQLstr = ' select @CountRs=count(*) from ' + @Tnamestr
    Else
        set @SQLstr = ' select @CountRs=count(*) from ' + @Tnamestr + ' where ' + @Wherestr
        exec sp_executesql @SQLstr ,N ' @CountRs int out ' , @CountRs out

    if @CountRs <= @PageSize
        set @Countpages = 1
    else
        begin
            set @Countpages = cast ( @CountRs / @PageSize as int )
            if ( @CountPages * @PageSize != @CountRs )
            set @Countpages = @CountRs / @PageSize + 1
        end
    -- print @CountRs
    -- print @Countpages
   END





GO