求一高效MSSQL分页存储过程

Question

最好把适用数量级也说一下，谢谢大家了

Answer 1

CREATE PROC sp_PageView
@tbname     sysname,             --要分页显示的表名
@FieldKey   nvarchar(1000),      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1,              --要显示的页码
@PageSize   int=10,              --每页的大小(记录数)
@FieldShow  nvarchar(1000)='',   --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder  nvarchar(1000)='',  --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where     nvarchar(1000)='',    --查询条件
@PageCount  int OUTPUT           --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
 RAISERROR(N'对象"%s"不存在',1,16,@tbname)
 RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
 RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
 RETURN
END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
 RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
 RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
 SET @FieldOrder=N''
ELSE
 SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
 SET @Where=N''
ELSE
 SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
 DECLARE @sql nvarchar(4000)
 SET @sql=N'SELECT @PageCount=COUNT(*)'
  +N' FROM '+@tbname
  +N' '+@Where
 EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
 @TopN1=@PageCurrent*@PageSize--第一页直接显示
IF @PageCurrent=1
 EXEC(N'SELECT TOP '+@TopN
  +N' '+@FieldShow
  +N' FROM '+@tbname
  +N' '+@Where
  +N' '+@FieldOrder)
ELSE
BEGIN
 --生成主键(惟一键)处理条件
 DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)
 SELECT @Where1=N'',@s=@FieldKey
 WHILE CHARINDEX(N',',@s)>0
  SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
   @Where1=@Where1
    +N' AND a.'+LEFT(@s,CHARINDEX(N',',@s)-1)
    +N'='+LEFT(@s,CHARINDEX(N',',@s)-1)
 SELECT @Where1=STUFF(@Where1+N' AND a.'+@s+N'='+@s,1,5,N''),
  @TopN=@TopN1-@PageSize --执行查询
 EXEC(N'SET ROWCOUNT '+@TopN1
  +N' SELECT '+@FieldKey
  +N' INTO # FROM '+@tbname
  +N' '+@Where
  +N' '+@FieldOrder
  +N' SET ROWCOUNT '+@TopN
  +N' DELETE FROM #'
  +N' SELECT '+@FieldShow
  +N' FROM '+@tbname
  +N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1
  +N') '+@FieldOrder)
END
GO

Answer 2

        /// <summary>
        /// 获取记录数
        /// </summary>
        /// <param name="table">表名或视图</param>
        /// <param name="cond">格式:Where ...</param>
        /// <returns></returns>
        public int GetRowCount(string table, string cond)
        {
            int rowcount = 0;
            string query = string.Format("Select count(*) From {0} {1}", table, cond);
            using (IDataReader reader = DbHelper.ExecuteReader(CommandType.Text, query))
            {
                if (reader.Read()) rowcount = reader.GetInt32(0);                return rowcount;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">表或视图</param>
        /// <param name="rowpage">分页索引</param>
        /// <param name="rowcount">分页大小</param>
        /// <param name="orderFid">排序字段</param>
        /// <param name="Conditions">检索条件</param>
        /// <returns></returns>
        public DataTable GetinfoList(string table, int rowpage, int rowcount, string orderFid, string Conditions)
        {            string query = "Select * From "
                + "(Select *,ROW_NUMBER() OVER(ORDER BY {2}) as RowNum From {4} {3}) as newTable "
                + "Where (RowNum BETWEEN ({0}) AND ({1}))";
            query = string.Format(query, (rowpage - 1) * rowcount + 1, rowpage * rowcount, orderFid, Conditions, table);            //string query = "Select top {0} * From {1} {2} and id not in(Select top {3} id From {1} {2} Order by {4}) Order by {4}";
            //query = string.Format(query, rowcount, table, Conditions, (rowpage - 1) * rowcount + 1, orderFid);
            return DbHelper.ExecuteTable(CommandType.Text, query);        }

Answer 3

如果是2005直接用rowsnumber()分页啊.

Answer 4

以前的一个人写的  千万级Create    PROCEDURE [dbo].[SP_Pagination]
/**//*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where 
7.Group :Group语句,不带Group By***************************************************************/
(
@Tables varchar(2000),
@PrimaryKey varchar(500),
@Sort varchar(500) = NULL,
@CurrentPage int = 1,
@PageSize int ,
@Fields varchar(2000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/**//*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(1000)
DECLARE @SortName varchar(1000)
DECLARE @strSortColumn varchar(1000)
DECLARE @operator char(2)
DECLARE @type varchar(1000)
DECLARE @prec int
/**//*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(500)
DECLARE @strStartRow varchar(500)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/**//*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/**//*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(500))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(500))
/**//*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/**//*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)

Answer 5

/****** 对象: 存储过程 dbo.sp_GetListByAllOfPrimaryKey    脚本日期: 2010-6-10 13:08:01 ******/
-- =============================================
-- Author: <Author,huangye>
-- Create date: <Create Date,2008-2-11>
-- Description: <Description,应用于所有表，读取所有符合要求的记录，不存在分页，但只支持有主键的表或者试图>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetListByAllOfPrimaryKey]
-- Add the parameters for the stored procedure here
@tblName varchar(255),        -- 表名
    @primarykey varchar(255),      --主键字段名
    @OrderType bit = 0,            -- 设置排序类型, 非0 值则降序
    @strWhere varchar(1000) = '' -- 查询条件(注意: 不要加where)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    declare @strSQL   varchar(6000)       -- 主语句
declare @strOrder varchar(400)        -- 排序类型
if @OrderType != 0
set @strOrder = ' order by ' + @primarykey +' desc'
else
set @strOrder = ' order by ' + @primarykey + ' asc'
print(@strOrder)
set @strSQL=''
if @strWhere !='' Or @strWhere != null
begin
set @strSQL='select * from '+ @tblName +' where '+ @strWhere + ' '+ @strOrder
end
else
begin
set @strSQL='select * from '+@tblName + ' ' + @strOrder
end
print (@strSQL)
exec (@strSQL)
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ONGO/****** 对象: 存储过程 dbo.sp_GetListByPageOfAnyField    脚本日期: 2010-6-10 13:08:01 ******/
-- =============================================
-- Author: <Author,huangye>
-- Create date: <Create Date,2008-12-11>
-- Description: <Description,查询分页,应用于所有表，支持按任意列排序>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetListByPageOfAnyField] 
-- Add the parameters for the stored procedure here
@tablename varchar(100), -- 表名或视图表
        @fieldlist varchar(4000) = '*', -- 欲选择字段列表
        @orderfield varchar(100), -- 排序字段
        @keyfield varchar(100), -- 主键
        @pageindex int, -- 页号,从开始
        @pagesize int=20, -- 页尺寸
        @strwhere varchar(4000) ='', -- 条件
        @ordertype bit=true -- 排序,1,降序,0,升序
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    declare @sqlstr varchar(6000) 
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
    set @strwhere=replace(@strwhere,'''','''''') 
    set @strwhere=replace(@strwhere,'--','') 
    set @strwhere=replace(@strwhere,';','') 
set @sqlstr='declare @curpagenum int;' 
    set @sqlstr=@sqlstr+'declare @nextpagenum int;' 
    set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';' 
    set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';' 
    set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);' 
    if @ordertype=1 
    begin 
if(@strwhere <> null and @strwhere <> '')
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from  
 
'+@tablename+'  where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (  
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';' 
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from  
 
'+@tablename+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (  
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+'  
 
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';' 
end
    end 
    else 
    begin 
if(@strwhere <> null and @strwhere <> '')
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from  
 
'+@tablename+'  where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (  
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'  
 
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';' 
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from  
 
'+@tablename+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (  
 
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+'  
 
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';' 
end
    end 
    set @sqlstr=@sqlstr+'execute( @sqlstr)' 
    --print @sqlstr 
    execute(@sqlstr)
end
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ONGO/****** 对象: 存储过程 dbo.sp_GetListByPageOfPrimaryKey    脚本日期: 2010-6-10 13:08:01 ******/
-- =============================================
-- Author: <Author,huangye>
-- Create date: <Create Date,2008-12-11>
-- Description: <Description,通用查询分页,应用于所有表，但只支持有主键的表或者试图>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetListByPageOfPrimaryKey]
-- Add the parameters for the stored procedure here
@tblName      varchar(255),        -- 表名
    @primarykey      varchar(255),      --主键字段名
    @PageSize     int = 1,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    bit = 0,            -- 设置排序类型, 非0 值则降序
    @strWhere     varchar(1000) = '' -- 查询条件(注意: 不要加where)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(1000)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @primarykey +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @primarykey + ' asc'
end
select @strSQL=''
set @strSQL = 'select top ' + str(@PageSize) + ' * from '
+ @tblName + ' where ' + @primarykey + '' + @strTmp + '('
+ @primarykey + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @primarykey + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + ' * from '
        + @tblName + ' where ' + @primarykey + ' ' + @strTmp + '('
        + @primarykey + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
        + @primarykey + ' from ' + @tblName + ' where ' + @strWhere + '' 
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from '
        + @tblName + ' ' + @strTmp + ' ' + @strOrder
end
PRINT (@strSQL)
exec (@strSQL)
ENDGO
/****** 对象: 存储过程 dbo.sp_GetListOfCount    脚本日期: 2010-6-10 13:08:01 ******/
-- =============================================
-- Author: <Author,huangye>
-- ALTER  date: <ALTER  Date,2008-9-14>
-- Description: <Description,通用查询分页,应用于所有表，但只支持有主键的表或者试图>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetListOfCount]
-- Add the parameters for the stored procedure here
    @tblName      varchar(255),        -- 表名
    @strWhere     varchar(1000) = '', -- 查询条件(注意: 不要加where)
    @RecordCount int output   -- 返回符合条件的记录数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @strSQLCount nvarchar(1000) 
select @strSQLCount=''

select @RecordCount =0 
if(@strWhere!='')
    set @strSQLCount = 'select @RecordCount = count(*) from ' + @tblName +' where ' + @strWhere+''
else
    set @strSQLCount = 'select @RecordCount = count(*) from ' + @tblName + ''

exec sp_executesql @strSQLCount,N'@RecordCount int output',@RecordCount output

ENDGO

Answer 6

基本上自从有了sql server2005的“开窗”功能，就省去了自己写一大堆用来开窗（好多的临时表，嵌套top、游标等等）的代码了！我支持使用窗口功能的row_number()方法，它比较简单。

Answer 7

不过我觉得没有必要一定写存储过程嘛！比如要查询从10000到10099行数据，可以简单些例如select f1,f2,f3,f4 from (select *,row_number() over (order by f1,f3 desc) as row from (s
elect top 10099 * from myTable where ......) as a) as t where row>=10000或者可以写得更简洁一点。直接从应用程序中写sql语句也是相当方便地。

Answer 8

http://topic.csdn.net/u/20101223/14/bb019bda-0851-4dfd-be2d-55b2e13463b8.html

Answer 9

千万级的数据。我都直接分页控件。
要不就是简洁的sql语句解决存储过程能不用尽量不用因为不会。。

Answer 10

create proc 电影存储过程 --翻页存储过程
@PageSize  int,   --每页面显示数据条数
@PageIndex  int,   --页面索引
@PageCount  int  output,  --总页数
@名称 nvarchar(100)
as
--查询范围  (@PageSize*@PageIndex+1) 起始行
-- @PageSize*(@PageIndex+1) 结束行select * from(select Row_Number() over (order by 影片ID) as id, *  from(
select * from 电影 where 名称 like '%'+@名称+'%' or 主演 like '%'+@名称+'%'  )a )b where  id  between (@PageSize*@PageIndex+1) and @PageSize*(@PageIndex+1)--得到数据行数
select @PageCount=count(1) from 电影 where 名称 like '%'+@名称+'%' or 主演 like '%'+@名称+'%'
--计算出总行数   
set @PageCount= (@PageCount-1)/@PageSize+1
print @PageCount