I have been browsing the newsgroups trying to find a good solution for this
problem.
I have a resultset and I need to show that information in pages. I also need
to have this sorted by a specific column (Date for example)
I found the following solution written by Don Arsenault that works very
well:
/*
The above routines assume that the resultset is ordered by the unique key.
If that's not true, a combination of a sort column and the unique key
can be used. Pass the sort column value as well as the unique key to the
next_page and previous_page procedures. Make sure the table has an index
on the combination of the sort column and the unqiue key.
*/
CREATE PROCEDURE next_page
@.current_page_last_row_key int,
@.current_page_last_row_sort int
AS
--return first page if parameters are null.
IF (@.current_page_last_row_key is null)
SELECT TOP 10 *
FROM my_big_table
ORDER BY sort_column, unique_key
ELSE
SELECT TOP 10 *
FROM my_big_table
WHERE
(sort_column >= @.current_page_last_row_sort)
and (
(sort_column > @.current_page_last_row_sort)
or (unique_key > @.current_page_last_row_key)
)
ORDER BY sort_column, unique_key
CREATE PROCEDURE previous_page
@.current_page_first_row_key int,
@.current_page_first_row_sort int
AS
--return last page if parameters are null.
IF (@.current_page_first_row_sort_key is null)
SELECT *
FROM
(
SELECT TOP 10 *
FROM my_big_table
ORDER BY sort_column DESC, unique_key DESC
) AS Reorder
ORDER BY unique_key
ELSE
SELECT *
FROM
(
SELECT TOP 10 *
FROM my_big_table
WHERE
(sort_column <= @.current_page_last_row_sort)
and (
(sort_column < @.current_page_last_row_sort)
or (unique_key < @.current_page_last_row_key)
)
ORDER BY sort_column DESC, unique_key DESC
) AS Reorder
ORDER BY sort_column, unique_key
That works great when you want to move from one page to the next (or to the
previous one), but I don't know
how I can go to a specific page. For example, go to page 100.
Do you guys know how I can get this?
ThanksYou may also want to refer to www.aspfaq.com/2120 for some ideas.
Anith
Tuesday, March 20, 2012
Paging with Stored Procedures
Labels:
browsing,
database,
microsoft,
mysql,
newsgroups,
oracle,
pages,
paging,
procedures,
resultset,
server,
solution,
sql,
stored,
thisproblem
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment