高效的SQLServer数据分页的通用存储过程

作者: jaclon 分类: 数据库 发布时间: 2010-11-10 00:57 ė 6没有评论
IF EXISTS(SELECT * FROM sysobjects 
WHERE name = 'COMMON_PROCEDURE_QueryWithPage' AND type = 'P')
DROP PROC COMMON_PROCEDURE_QueryWithPage
GO
CREATE PROC COMMON_PROCEDURE_QueryWithPage
(
    @SqlStr        VARCHAR(5000),
    @PrimaryKey    VARCHAR(100),
    @OrderStr      VARCHAR(500),
    @CurrPageNo    INT,
    @PageSize      INT,
    @TotalNum      INT OUTPUT
)
AS
DECLARE
@ExeSql VARCHAR(8000)
SET NOCOUNT ON
IF @TotalNum = -1
CREATE TABLE #Table_Count(TotalNum INT)
SET @ExeSql =
'SELECT IDENTITY(INT) AS RowIndex, A.' + @PrimaryKey + CHAR(10) +
'INTO #Table_Data
FROM (' + @SqlStr + ') AS A'
IF @OrderStr <> ''
BEGIN
SET @ExeSql = @ExeSql + CHAR(10) +
'ORDER BY ' + @OrderStr
END
IF @TotalNum = -1
BEGIN
SET @ExeSql = @ExeSql + CHAR(10) + CHAR(10) +
'INSERT INTO #Table_Count VALUES(ISNULL(SCOPE_IDENTITY(), 0))'
END
SET @ExeSql = @ExeSql + 'SELECT *
FROM (' + @SqlStr + ') AS A
WHERE A.' + @PrimaryKey + ' IN(
SELECT ' + @PrimaryKey + ' FROM #Table_Data WHERE RowIndex BETWEEN ' +
CONVERT(VARCHAR, (@CurrPageNo-1)*@PageSize+1) + ' AND ' + CONVERT(VARCHAR, @CurrPageNo*@PageSize) + ')'
IF @OrderStr <> ''
BEGIN
SET @ExeSql = @ExeSql + CHAR(10) +
'ORDER BY ' + @OrderStr
END
EXEC(@ExeSql)
IF @TotalNum = -1
BEGIN
SELECT @TotalNum = Totalnum FROM #Table_Count
DROP TABLE #Table_Count
SET @TotalNum = -1
END
RETURN
GO

介绍

功能: 对传进来的查询SQL进行分页后返回指定页数据, 不管查询多少页,其查询性能只受 @SqlStr 自身性能的影响,有时还要快
参数: @SqlStr 需要执行的查询语句
@PrimaryKey 参数@SqlStr返回列中具有唯一确定性的字段名,不能有表别名前缀
@OrdrStr 参数@SqlStr返回列中存的字段,用于排序,不能有表别名前缀
@CurrPageNo 需要返回的页数
@PageSize 每页行数
@TotalNum 当传进来为 -1 时,返回符合条件的总行数
author: 杨连山
date: 2008-01-25
测试代码:

SELECT GETDATE()
DECLARE
@SqlStr     VARCHAR(8000),
@CurrPageNo INT,
@PageSize   INT,
@TotalNum   INT
SET @SqlStr =
''SELECT *
FROM PROD_PRODUCT WITH(NOLOCK)
WHERE CONTAINS(ProductName,  ''''手机'''')''
SET @CurrPageNo = 700
SET @PageSize = 20
SET @TotalNum =  -1
EXEC COMMON_PROCEDURE_QueryWithPage @SqlStr, ''ProductPKId'',  
''UpdateDateTime'', @CurrPageNo, 20, @TotalNum output
SELECT @TotalNum,  GETDATE()

以上摘自:http://dev.firnow.com/course/7_databases/sql/sqlServer/2008224/101110.html

本人在asp程序中的使用心得

1.没有办法同时得到记录总数和当前记录集,在使用ADODB.Command时出现了一些问题,具体因原不清楚

2.事实上应该先取得记录总数,然后才能正确判断当前是第几页

本人建议对此存储过程作一些调整

1.当@TotalNum = -1时只取得总记录数,否则只返回当前记录集

2.临时表中的@PrimaryKey应该重命名一下,防止在按主键排序时出现问题

本文出自 肥蕉博客@IT技术,热门话题,生活随笔,美图欣赏,转载时请注明出处及相应链接。

本文永久链接: http://www.bananawolf.com/html/2010/11/15.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

Ɣ回顶部