Stored procedure, as described above, might look like:
CREATE PROCEDURE paging
@n int -- number of records per page
, @p int =1 -- the page number by default - the first
AS
SELECT * FROM
(SELECT *,
CASE WHEN num % @n = 0 THEN num/@n ELSE num/@n + 1 END AS page_num,
CASE WHEN total % @n = 0 THEN total/@n ELSE total/@n + 1 END AS num_of_pages
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY price DESC) AS num,
COUNT(*) OVER() AS total FROM Laptop
) X
) Y
WHERE page_num = @p;
GO
Thus, if we need to get a second page with 2 entries on the page, just write
As a result, we obtain:
| code
|
model
|
speed
|
ram
|
hd
|
price
|
screen
|
num
|
total
|
page_num
|
num_of_pages
|
| 4 | 1298 | 600 | 64 | 10 | 1050.00 | 15 | 3 | 6 | 2 | 3 |
| 2 | 1321 | 500 | 64 | 8 | 970.00 | 12 | 4 | 6 | 2 | 3 |
|
But so will look incomplete second page, if the number of records per page will be equal to four:
| code
|
model
|
speed
|
ram
|
hd
|
price
|
screen
|
num
|
total
|
page_num
|
num_of_pages
|
| 6 | 1298 | 450 | 64 | 10 | 950.00 | 12 | 5 | 6 | 2 | 2 |
| 1 | 1298 | 350 | 32 | 4 | 700.00 | 11 | 6 | 6 | 2 | 2 |
|
go
