MSSQL分页存储过程完整示例(支持多表分页存储)

本文实例讲述了mssql分页存储过程。分享给大家供大家参考，具体如下：

use [db_common]
go
/****** 对象: storedprocedure [dbo].[com_pagination]  脚本日期: 03/09/2012 23:46:20 ******/
set ansi_nulls on
go
set quoted_identifier on
go
/************************************************************
 *
 *sql分页存储过程（支持多表分页存储）
 *
 *调用实例：
 exec com_pagination 100, --总记录数
   0, --总页数
    -- 'person',--查询的表名
   '
           person p
           left join te a
           on a.pid=p.id 
          ', --查询的表名（这里为多表）
   'a.*', --查询数据列
   'p.id', --排列字段
   'p.id', --分组字段
   2, --每页记录数
   1, --当前页数
   0, --是否使用分组，否是
   ' a.pid=2'--查询条件
 ************************************************************/
create procedure [dbo].[com_pagination]
@totalcount int output, --总记录数
@totalpage int output, --总页数
@table nvarchar(1000), --查询的表名（可多表，例如：person p left join te a on a.pid=p.id ）
@column nvarchar(1000), --查询的字段，可多列或者为*
@ordercolumn nvarchar(100), --排序字段
@groupcolumn nvarchar(150), --分组字段
@pagesize int, --每页记录数
@currentpage int, --当前页数
@group tinyint, --是否使用分组，否是
@condition nvarchar(4000) --查询条件（注意：若这时候为多表查询，这里也可以跟条件，例如：a.pid=2）
as
declare @pagecount   int, --总页数
    @strsql    nvarchar(4000), --主查询语句
    @strtemp    nvarchar(2000), --临时变量
    @strcount   nvarchar(1000), --统计语句
    @strordertype nvarchar(1000) --排序语句
begin
set @pagecount = @pagesize * (@currentpage -1)
set @strordertype = ' order by ' + @ordercolumn + ' '
if @condition != ''
begin
  if @currentpage = 1
  begin
    if @group = 1
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
        + ' where ' + @condition + ' group by ' + @groupcolumn
      set @strcount = @strcount + ' set @totalcount=@@rowcount'
      set @strsql = 'select top ' + str(@pagesize) + ' ' + @column 
        + ' from ' + @table + ' where ' + @condition + 
        ' group by ' + @groupcolumn + ' ' + @strordertype
    end
    else
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
        + ' where ' + @condition
      set @strsql = 'select top ' + str(@pagesize) + ' ' + @column 
        + ' from ' + @table + ' where ' + @condition + ' ' + @strordertype
    end
  end
  else
  begin
    if @group = 1
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
        + ' where ' + @condition + ' group by ' + @groupcolumn
      set @strcount = @strcount + ' set @totalcount=@@rowcount'
      set @strsql = 'select * from (select top (2000) ' + @column 
        + ',row_number() over(' + @strordertype + 
        ') as num from ' + @table + ' where ' + @condition + 
        ' group by ' + @groupcolumn + 
        ') as t where num between ' + str(@pagecount + 1) + 
        ' and ' + str(@pagecount + @pagesize)
    end
    else
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
        + ' where ' + @condition
      set @strsql = 'select * from (select top (2000) ' + @column 
        + ',row_number() over(' + @strordertype + 
        ') as num from ' + @table + ' where ' + @condition + 
        ') as t where num between ' + str(@pagecount + 1) + 
        ' and ' + str(@pagecount + @pagesize)
    end
  end
end
else
  --没有查询条件
begin
  if @currentpage = 1
  begin
    if @group = 1
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
        + ' group by ' + @groupcolumn
      set @strcount = @strcount + 'set @totalcount=@@rowcount'
      set @strsql = 'select top ' + str(@pagesize) + ' ' + @column 
        + ' from ' + @table + ' group by ' + @groupcolumn + ' ' + 
        @strordertype
    end
    else
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
      set @strsql = 'select top ' + str(@pagesize) + ' ' + @column 
        + ' from ' + @table + ' ' + @strordertype
    end
  end
  else
  begin
    if @group = 1
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
        + ' group by ' + @groupcolumn
      set @strcount = @strcount + 'set @totalcount=@@rowcount'
      set @strsql = 'select * from (select top (2000) ' + @column 
        + ',row_number() over(' + @strordertype + 
        ') as num from ' + @table + ' group by ' + @groupcolumn + 
        ') as t where num between ' + str(@pagecount + 1) + 
        ' and ' + str(@pagecount + @pagesize)
    end
    else
    begin
      set @strcount = 'select @totalcount=count(*) from ' + @table
      set @strsql = 'select * from (select top (2000) ' + @column 
        + ',row_number() over(' + @strordertype + 
        ') as num from ' + @table + ') as t where num between ' + 
        str(@pagecount + 1) + ' and ' + str(@pagecount + @pagesize)
    end
  end
end
exec sp_executesql @strcount,
   n'@totalcount int output',
   @totalcount output
if @totalcount > 2000
begin
  set @totalcount = 2000
end
if @totalcount%@pagesize = 0
begin
  set @totalpage = @totalcount / @pagesize
end
else
begin
  set @totalpage = @totalcount / @pagesize + 1
end
set nocount on
exec (@strsql)
end
set nocount off
/**调用实例：
exec com_pagination 100, --总记录数
   0, --总页数
    -- 'person',--查询的表名
   '
           person p
           left join te a
           on a.pid=p.id 
          ', --查询的表名（这里为多表）
   'a.*', --查询数据列
   'p.id', --排列字段
   'p.id', --分组字段
   2, --每页记录数
   1, --当前页数
   0, --是否使用分组，否是
   ' a.pid=2'--查询条件
select a.* 
from  person p
    left join te a
      on a.pid = p.id
where a.pid = 2
**/

希望本文所述对大家sql server数据库程序设计有所帮助。