What is Pagination
Pagination is the process of displaying a subset of the data on a web page at one time. The simplest example is a google search result in that after a search is performed you are displayed with the first x results as page 1. Then utilizing the page handlers you are able to view page 2, 3, etc until there are no additional results.
Pagination and Server Side Code
When developing an application the back end is responsible for obtaining and storing the requested data to be presented to the user. There are two obtions available to developers when dealing with pagination. The first is to obtain all the data and save it in the session for which only subsets of it will be displayed depending on the page the user is viewing. While the second method is to obtain only the data that is for the current page and when a new page is requested then that data is obtained.
This article deals with the second method as the smaller data set will allow for the results to be returned to the user faster as well as having a smaller strain on the resources of the server.
Limitations of Sybase
When utilizing a Sybase database as the data warehouse for your application there are difficulties in creating this dynamic pagination as there is no LIMIT or OFFSET commands in the Transact-SQL (T-SQL) language. In other SQL languages these commands allow you to specify how many results you want (LIMIT) and where to start returning your results from (OFFSET). To overcome this limitation we will have to make use of a Stored Procedure to manipulate data into and out of a temporary database.
Implementation of Pagination
For the purposes of this tutorial lets assume we are working with a User table created by the following statement:
create table T_USER ( ID int identity, USER_NAME varchar(50), LAST_NAME varchar(50), FIRST_NAME varchar(50), unique clustered (ID) )
From this table we are going to want to obtain x users starting with the yth one. So to begin lets create a stored procedure that takes 2 parameters pageSize and offset.
CREATE PROCEDURE USER_PAGINATION @offset int, @pageSize int, AS BEGIN //--content END
Now to perform the pagination we are going to have to perform 2 queries. The first query is going to select every user in the Users table ordered by their USER_NAME and insert them into a temp table with a new identity column that equals their order in the temp table.
SELECT ID_KEY = identity(5) USER_NAME, FIRST_NAME, LAST_NAME INTO #TEMPUTBL FROM T_USER ORDER BY USER_NAME
The second query will then select the rows from the temp table beginning with the offset upto the offset + pageSize
SELECT * FROM #TEMPUTBL WHERE ID_KEY > @offset and ID_KEY <= (@offset + @pageSize)
Putting the whole thing together we end up with:
CREATE PROCEDURE USER_PAGINATION @offset int, @pageSize int, AS BEGIN SELECT ID_KEY = identity(5) USER_NAME, FIRST_NAME, LAST_NAME INTO #TEMPUTBL FROM T_USER ORDER BY USER_NAME SELECT * FROM #TEMPUTBL WHERE ID_KEY > @offset and ID_KEY <= (@offset + @pageSize) END
Last step is simply to run the stored procedure passing the pageSize and the offset.
execute USER_PAGINATION 50, 25