欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-09 23:49:15
本文实例讲述了mssql分页存储过程。分享给大家供大家参考,具体如下: use [db_common] go /****** 对象: storedproced...

本文实例讲述了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数据库程序设计有所帮助。