sql server programming: SQL2k record paging -- sql server programming

SQL2k record paging

Eddie
6/26/2005 10:22:28 PM

sql server programming:

I found this article at http://www.aspfaq.com/show.asp?id=2120 which
shows different ways to page records from SQL Server 2k. I chose the
stored procedure method call "Stored Procedure Rowcount".

Here is the sql code and it works great with their example. The
SampleCDs table is set up so that the ArtistName and Title are combined
into a primary key. But when I add an autoid identity column to this
table which I called CDId and I remove the primary key status for
ArtistName and Title and put it on the CDId column then the whole thing
breaks. How would I adjust lines 36 to 61 to reflect the new primary key
autoid column called CDId?

Thanks!

Ed

You can go to the artilce to look at the script that creates the
SampleCDs table

01: CREATE PROCEDURE SampleCDs_Paging_Rowcount
02: @pagenum INT = 1,
03: @perpage INT = 50
04: AS
05: BEGIN
06: SET NOCOUNT ON
07:
08: DECLARE
09: @ubound INT,
10: @lbound INT,
11: @pages INT,
12: @rows INT
13:
14: SELECT
15: @rows = COUNT(*),
16: @pages = COUNT(*) / @perpage
17: FROM
18: SampleCDs WITH (NOLOCK)
19:
20: IF @rows % @perpage != 0 SET @pages = @pages + 1
21: IF @pagenum < 1 SET @pagenum = 1
22: IF @pagenum > @pages SET @pagenum = @pages
23:
24: SET @ubound = @perpage * @pagenum
25: SET @lbound = @ubound - (@perpage - 1)
26:
27: SELECT
28: CurrentPage = @pagenum,
29: TotalPages = @pages,
30: TotalRows = @rows
31:
32: -- this method determines the string values
33: -- for the first desired row, then sets the
34: -- rowcount to get it, plus the next n rows
35:
36: DECLARE @aname VARCHAR(64), @title VARCHAR(64)
37:
38: SET ROWCOUNT @lbound
39:
40: SELECT
41: @aname = ArtistName,
42: @title = Title
43: FROM
44: SampleCDs WITH (NOLOCK)
45: ORDER BY
46: ArtistName,
47: Title
48:
49: SET ROWCOUNT @perPage
50:
51: SELECT
52: ArtistName,
53: Title
54: FROM
55: SampleCDs WITH (NOLOCK)
56: WHERE
57: ArtistName + '~' + Title
58: >= @aname + '~' + @title
59: ORDER BY
60: ArtistName,
61: Title
62:
63: SET ROWCOUNT 0
64: END
65: GO

Re: SQL2k record paging

Roji. P. Thomas
6/27/2005 12:00:00 AM

Here you go

31:
32: -- this method determines the string values
33: -- for the first desired row, then sets the
34: -- rowcount to get it, plus the next n rows
35:
36: DECLARE @cdId bigint
37:
38: SET ROWCOUNT @lbound
39:
40: SELECT
41: @cdId = CDId
42:
43: FROM
44: SampleCDs WITH (NOLOCK)
45: ORDER BY
46: CdId
47:
48:
49: SET ROWCOUNT @perPage
50:
51: SELECT
52: ArtistName,
53: Title
54: FROM
55: SampleCDs WITH (NOLOCK)
56: WHERE
57: CdId > @CdId
58:
59: ORDER BY
60: CdId
61:
62:
63: SET ROWCOUNT 0

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com

[quoted text, click to view]