需要使用SQL Server 2005转换大型数据集的分页存储过程

Right now the code is (to keep things simple I didn't use the ADO Command Object yet to prevent SQL injection).

现在代码是(为了简单起见,我还没有使用ADO命令对象来防止SQL注入)。

I have a large dataset. I'm using getrows method which is awesome to retrieve the recordsets. I can't find a similar stored procedure to retrieve records based on which page you are own within the browser.

我有一个大型数据集。我正在使用getrows方法,它很棒,可以检索记录集。我找不到类似的存储过程来根据您在浏览器中拥有的页面来检索记录。

Basically the sql retrieves 10 recordsets relative to the page the clients browser is on. It gets the page value from the CurrPage value. I handle the first page and last page with server side code so there arent any errors.

基本上,sql检索相对于客户端浏览器所在页面的10个记录集。它从CurrPage值获取页面值。我使用服务器端代码处理第一页和最后一页,因此没有任何错误。

Any help is appreciated.

任何帮助表示赞赏。

If IsEmpty(Request.Querystring("pg")) then
CurrPage = 1
Else
CurrPage = Cint(Request.Querystring("pg"))
End If

RSPrevPage = CurrPage -1
RSNextPage = CurrPage + 1 

SQL = "SELECT gallerypublic.img, gallerypublic.galleryID, blahblahblah FROM gallerypublic INNER JOIN GalleryPublicCat ON gallerypublic.publicgallerycatid = GalleryPublicCat.pubcatID INNER JOIN userbase ON gallerypublic.userid = userbase.userid Order by galleryid desc"

Set rsFeed = Server.CreateObject("ADODB.Recordset")
rsFeed.Open sql, Conn, adOpenKeyset, adLockReadOnly
rsFeed.PageSize = 10
rsFeed.AbsolutePage = CurrPage
arrFeed = rsFeed.getrows(10)
intPageCount = rsFeed.PageCount
rsFeed.close
set rsFeed = Nothing

I found this stored procedure on http://www.aspfaqs.com/webtech/042606-1.shtml but I can't translate it to my needs.

我在http://www.aspfaqs.com/webtech/042606-1.shtml上找到了这个存储过程,但我无法将其转换为我的需求。

TIA

After a little trial and error, here's the working code:

经过一些试验和错误,这是工作代码:

Server Side:

    If IsEmpty(Request.Querystring("pg")) then
    CurrPage = 1
    Else
    CurrPage = Cint(Request.Querystring("pg"))
    End If

    RSPrevPage = CurrPage -1
    RSNextPage = CurrPage + 1

    pgSize = 10

    Set objCommandSec = CreateObject("ADODB.Command") 
    objCommandSec.ActiveConnection = Conn

    With objCommandSec
    Set .ActiveConnection = Conn
    .CommandType = adCmdStoredProc
    .CommandText = "spPageDef"
    .Parameters.Append .CreateParameter("@PageNum", 200, 1, 255, CurrPage)
    .Parameters.Append .CreateParameter("@PageSize", 200, 1, 255, pgSize)
    .Parameters.Append .CreateParameter("@TotalRowsNum", adInteger, adParamReturnValue)

    Set rsFeed = objCommandSec.Execute
    arrFeed = rsFeed.getrows()
    rsFeed.close
    set rsFeed = nothing

    intPageCount = cLng((.Parameters(2).value/pgSize))

    End With

Stored Procedure:

    @PageNum int,
    @PageSize int,
    @TotalRowsNum int output

    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        -- Use ROW_NUMBER function

        WITH DefaultEntries As
        (
            SELECT  
g.img, g.galleryID, g.viewed, g.votes, g.rate, g.created, blahblah,
'RowNumber' = ROW_NUMBER() OVER(ORDER BY galleryid DESC)

        FROM gallerypublic AS g INNER JOIN GalleryPublicCat AS gpc ON g.publicgallerycatid = gpc.pubcatID INNER JOIN userbase AS u ON g.userid = u.userid
        )

        -- Query result

        SELECT * 
        FROM DefaultEntries
        WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize             
        ORDER BY galleryid DESC

    SELECT @TotalRowsNum = count(galleryid) 
    FROM gallerypublic AS g INNER JOIN GalleryPublicCat AS gpc ON g.publicgallerycatid = gpc.pubcatID INNER JOIN userbase AS u ON g.userid = u.userid

    END

Thanks to all who helped out and pointed me in the correct direction

感谢所有帮助过的人,并指出了正确的方向

2 个解决方案

#1

Sounds like you want your paging logic within your stored proc. It looks like you're using SQL Server 2005. Suggest using a CTE to implement your paging, and ditch your ADO implementation. Suggest something like this:

听起来你想要在你的存储过程中使用你的分页逻辑。看起来你正在使用SQL Server 2005.建议使用CTE来实现你的分页,并放弃你的ADO实现。建议这样的事情:

CREATE PROC     GetMyData
@PageSize INT,
@PageNumber INT,
@FirstRow INT
AS
DECLARE @LastRow INT;

SELECT   @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
          @LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

WITH MySet  AS
(
    SELECT ROW_NUMBER() OVER
               (ORDER BY galleryid DESC) AS RowNumber,
            g.img, 
            g.galleryID, 
            etc 
    FROM gallerypublic as g
    INNER JOIN GalleryPublicCat AS gpc ON g.publicgallerycatid = gpc.pubcatID 
    INNER JOIN userbase AS u ON g.userid = u.userid 
)
SELECT  RowNumber, 
        img, galleryid, etc
FROM    MySet
WHERE   RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC;

#2

Stored Procedure

    @PageNum int,
    @PageSize int,
    @TotalRowsNum int output

    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        -- Use ROW_NUMBER function

        WITH DefaultEntries As
        (
            SELECT  
g.img, g.galleryID, g.viewed, g.votes, g.rate, g.created, blahblah,
'RowNumber' = ROW_NUMBER() OVER(ORDER BY galleryid DESC)

        FROM gallerypublic AS g INNER JOIN GalleryPublicCat AS gpc ON g.publicgallerycatid = gpc.pubcatID INNER JOIN userbase AS u ON g.userid = u.userid
        )

        -- Query result

        SELECT * 
        FROM DefaultEntries
        WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize             
        ORDER BY galleryid DESC

    SELECT @TotalRowsNum = count(galleryid) 
    FROM gallerypublic AS g INNER JOIN GalleryPublicCat AS gpc ON g.publicgallerycatid = gpc.pubcatID INNER JOIN userbase AS u ON g.userid = u.userid

    END

Server Side

If IsEmpty(Request.Querystring("pg")) then
CurrPage = 1
Else
CurrPage = Cint(Request.Querystring("pg"))
End If

RSPrevPage = CurrPage -1
RSNextPage = CurrPage + 1

pgSize = 10

Set objCommandSec = CreateObject("ADODB.Command") 
objCommandSec.ActiveConnection = Conn

With objCommandSec
Set .ActiveConnection = Conn
.CommandType = adCmdStoredProc
.CommandText = "spPageDef"
.Parameters.Append .CreateParameter("@PageNum", 200, 1, 255, CurrPage)
.Parameters.Append .CreateParameter("@PageSize", 200, 1, 255, pgSize)
.Parameters.Append .CreateParameter("@TotalRowsNum", adInteger, adParamReturnValue)

Set rsFeed = objCommandSec.Execute
arrFeed = rsFeed.getrows()
rsFeed.close
set rsFeed = nothing

intPageCount = cLng((.Parameters(2).value/pgSize))

End With