October 20, 2004 at 2:40 pm
We found a posting on how to solve paging of large recordset, but after reviewing the code we noticed three variables that we can not find the official meaning of, (CurrentPage, TotalPages, TotalRows). Can anyone reference a site or tell us want the variables do? Code below
CREATE PROCEDURE SampleCDs_Paging_Rowcount
@pagenum INT = 1,
@perpage INT = 50
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT
SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
SampleCDs WITH (NOLOCK)
IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum < 1 SET @pagenum = 1
IF @pagenum > @pages SET @pagenum = @pages
SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)
SELECT
CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows
-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows
DECLARE @aname VARCHAR(64), @title VARCHAR(64)
SET ROWCOUNT @lbound
SELECT
@aname = ArtistName,
@title = Title
FROM
SampleCDs WITH (NOLOCK)
ORDER BY
ArtistName,
Title
SET ROWCOUNT @perPage
SELECT
ArtistName,
Title
FROM
SampleCDs WITH (NOLOCK)
WHERE
ArtistName + '~' + Title
>= @aname + '~' + @title
ORDER BY
ArtistName,
Title
SET ROWCOUNT 0
END
GO
October 20, 2004 at 7:59 pm
They're not variables. It looks like the procedure returns two resultsets. The first resultset is one row and three columns with the CurrentPage, TotalPages, TotalRows. You need that info to determine how many times to exec the procedure. The second resultset is the actual page of data.
This method of paging looks pretty inefficient. I hope the table SampleCDs is pretty small.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply