求一高效MSSQL分页存储过程

最好把适用数量级也说一下，谢谢大家了 --------------------编程问答--------------------



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

--------------------编程问答--------------------



        /// <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);



        }

--------------------编程问答-------------------- 如果是2005直接用rowsnumber()分页啊. --------------------编程问答-------------------- 以前的一个人写的千万级

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 + '

'

)

--------------------编程问答--------------------





/****** 对象: 存储过程 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 ON



GO



/****** 对象: 存储过程 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 ON



GO



/****** 对象: 存储过程 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)

END



GO





/****** 对象: 存储过程 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

	

END



GO

--------------------编程问答-------------------- 哈哈，搜一下，很多！ --------------------编程问答-------------------- 基本上自从有了sql server2005的“开窗”功能，就省去了自己写一大堆用来开窗（好多的临时表，嵌套top、游标等等）的代码了！

我支持使用窗口功能的row_number()方法，它比较简单。 --------------------编程问答-------------------- 不过我觉得没有必要一定写存储过程嘛！比如要查询从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语句也是相当方便地。 --------------------编程问答-------------------- http://topic.csdn.net/u/20101223/14/bb019bda-0851-4dfd-be2d-55b2e13463b8.html --------------------编程问答-------------------- 学习2了~ --------------------编程问答-------------------- 真复杂，不知道效率怎么样 --------------------编程问答-------------------- 千万级的数据。。。。。。。我都直接分页控件。
要不就是简洁的sql语句解决存储过程能不用尽量不用因为不会。。 --------------------编程问答-------------------- 我的网摘中有 --------------------编程问答--------------------

引用 8 楼 sp1234 的回复:

不过我觉得没有必要一定写存储过程嘛！比如要查询从10000到10099行数据，可以简单些例如SQL code
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 ……

最近忙，前两天没来看

我们公司项目要求必须存储过程，没办法啊 --------------------编程问答--------------------



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

--------------------编程问答-------------------- 先不结贴了，等下我抽空好好看看

补充：.NET技术 ,  ASP.NET