SQL Server Paging and displaying Paged results on HTML Page

This is a situation which every SQL Server developer might face once in his lifetime. How to retrieve results based upon row counts? and display them on HMTL Pages. For the dismay SQL Server does not provide functionalities like MySQL LIMIT/OFFSET which makes it a bit more complicated. But my simple trick allows you to do paging by using simple SELECT statement combined with TOP. Apart from SQL Paging I have also included code to implement paging in HTML Pages so if you are using some other database just change the query appropriately and you are good to go.

Suppose you have to list all the customers from some database table filtered by some search string and the user submits the search string from an HTML form which is passed on to the search page as URL string (search.aspx?search=searchstring). Search page then displays results (MAX_RESULTS) on each page. To keep track of the number of displayed results we can use another field num in the URL String. So the initial search string will look like:

search.aspx?search=searchstring&num=0

Now to implement the search.aspx code we need to get the variables from the URL String and set the MAX_RESULTS limit:

Dim search As String = Request.QueryString("search")
Dim num As Integer = CInt(Request.QueryString("num"))
If num < 0 Then num = 0
Dim MAX_RESULTS As Integer = 10

Next step is to format the SQL Query. I have used nested Selects to restrict the results by not selecting the results matching the inner Select statement. The second Select statement just selects the number of rows after which we need to display the results:

Dim where As String = "Firstname LIKE '%" & search & "%'" 
Dim cmdString As String = "SELECT TOP " & MAX_RESULTS & " * FROM CUSTOMERS" & _
                          "WHERE CustomerId NOT IN (" & _
                          "SELECT TOP " & num & " CustomerId FROM CUSTOMERS" & _
                          "WHERE " & where & _
                          "ORDER BY FirstName )" & _
                          " AND " & where & _
                          "ORDER BY FirstName"

After executing the above query and displaying the results we need to display the links for Next and Previous page navigation. To do that we first need to get the total count (total_count) of results from the database using the following query:

cmdString = "select count(*) from CUSTOMERS WHERE " & where

To format the Previous and Next page URL’s with Next and Previous results left we have used two variables Next_URL and Prev_URL as follows:

Dim Next_URL As String = ""
Dim Prev_URL As String = ""
Dim Nav_Table As String = ""
If total_count > MAX_RESULTS Then 
  Dim next_count = num + MAX_RESULTS 
  Dim prev_count = num – MAX_RESULTS
  If num = 0 Then
    Next_URL = "<a href=""search.aspx?search=" & search & "&num=" & next_count & """>Next(" & total_count - next_count & ")&gt;</a>"
    Prev_URL = "&nbsp;"

  ElseIf next_count > total_count Then
    Next_URL = "&nbsp;"
    Prev_URL = "<a href=""search.aspx?search=" & search & "&num=" & prev_count & """>&lt;(" & num & ")Prev</a>"

  Else
    Next_URL = "<a href=""search.aspx?search=" & search & "&num=" & next_count & """>Next(" & total_count - next_count & ")&gt;</a>"
    Prev_URL = "<a href=""search.aspx?search=" & search & "&num=" & prev_count & """>&lt;(" & num & ")Prev</a>"
  End If
End If

If you are using some other database system or programming language, you just need to change the SQL statement appropriately and use the same logic to display the results in html.

Technorati Tags: SQL Server Paging