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 y^th 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

Resources

• Transact-SQL User’s Guide