Everytime I need to page through a large amount of SQL data I end up having to search for a standard code template I use. Usually I can find it buried in some project I worked on before. Tonight I couldn’t find it quickly and began to search online for it.
God bless the bloggers. Last time I searched on paging SQL was before the blogsphere was well established and it took me quite a while to find a decent solution. This time I found it quickly. Not only that, but I found a better one. It’s a stored proc that uses ROWCOUNT to determine the last record from the previous page (so you can select all records that occur after it) and again to limit the number of records returned. No need for a TOP statement so you can throw it into a stored procedure. It's really fast and efficient. This is pure genius. Thank you interweb!!! Thank you blobbers!!!
And let’s not forget Chris Hohmann, it’s his kung fu code sample. Take that Chuck Norris (yes, I still laugh frequently at that Chuck Norris post I made)!!!
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
Found at: #2120 : How do I page through a recordset?
-Eric Marvets