Paging in Web Applications (SQL Trick)

Web Applications here means the applications that are used through browsers. Web applications are tougher to develop and use as compared to windows applications. In this topic, I want to put focus on paging techniques for web applications.

If you are a web programmer, you may be having problem to display a large report or data in a data grid with paging. Paging is required as it might not be practical to display large no. of rows in a single grid. User will have to keep scrolling down and up. Also, a large memory will be constantly consumed. There are no. of ways to use paging in data grids. Here, I would like to focus on data grid displayed by pulling data from RDBMS database like SQL Server.

Suppose you are running a stored procedure (say sp_report1) to pull data from SQL Server database and it returns all data (say 5000 rows) at one time. A simple way would be to pass two input variables like @from_row_no and @to_row_no to the procedure and modify it to return rows of data from @from_row_no to @to_row_no.

This also can be done two ways. In first way, you put all output data in a temporary table which also contain an identity column. Then, while running final select statement from that final output table, use conditions to display from @from_row_no to @to_row_no.

Here is an example.

Create proc sp_Report1

( @from_row_no int, @to_row_no int)

as

begin

create table #tbl_final_display

(

id  int identity(1,1),

col1 varchar(30),

col2 varchar(30)

)

/* now insert data into #tbl_final_display as per your requirement */

-- for final select

select *

from #tbl_final_display

where id >= @from_row_no

and id <= @to_row_no

end

If you have to display results in order then you will have to pass ordering column name also to stored procedure. If the data in #tbl_final_display is going in single statement you can use order by clause in that statement with passed input variable. If not, you will have to create two temporary tables. First one to just hold the data and second one to copy data from first one by using order by clause. The second table will obviously have one more column than first one for id field. This process becomes tedious when there are many sortable columns as many if else conditions are required.

Here "id int identity(1,1)" means that id column of the table will have datatype int and the values will be automatically inserted while inserting rows in that column. The values will start from 1 and will keep on increasing by 1 for each new row.

In Second Method, you need to use the permanent table in which data are kept temporary. The stored procedure will just populate the table. Final select from table is run from front end like this.

For example to retrieve data from table tbl_final_display from row 9 to row 14 order by first field, you can use this query.

select    *
from    (
    select    top 6 *
    from    (
        select    top 14 *
        from    tbl_final_display
        order by 1
    ) tmp
    order by 1 desc
    ) tmp
order by 1

It uses multiple sub-queries and multiple select. First it selects 14 rows (@to_row_no) and then selects last 6 rows from that select. This has to be done from front end as while using "top 6 " we cannot pass variable instead of 6. It may be tedious for programmers to handle from front-end as much additional care must be taken. Such as provide fields to distinguish multiple users to eradicate the chances of wrong output when same report is used by multiple users.