loading..
Русский    English
04:26

Paging records page 3

Stored procedure, as described above, might look like:

  1. CREATE PROCEDURE paging
  2. @n int -- number of records per page
  3. , @p int =1 -- the page number by default - the first
  4. AS
  5. SELECT * FROM
  6.  (SELECT *,
  7.    CASE WHEN num % @n = 0 THEN num/@n ELSE num/@n + 1 END AS page_num,
  8.    CASE WHEN total % @n = 0 THEN total/@n ELSE total/@n + 1 END AS num_of_pages
  9.   FROM
  10.   (SELECT *,
  11.          ROW_NUMBER() OVER(ORDER BY price DESC) AS num,
  12.          COUNT(*) OVER() AS total FROM Laptop
  13.   ) X
  14.  ) Y
  15. WHERE page_num = @p;
  16. GO

Thus, if we need to get a second page with 2 entries on the page, just write

  1. exec paging @n=2, @p=2

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:

  1. exec paging @n=4, @p=2

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
Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
https://exchangesumo.com/obmen/PRUSD-RFBUAH/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100