paging issue

[moskhan2]

Hi,
I have recently written some stored procedures in sql server and I have run into the following problem for which I cannot find any solution .

Here is the scenario:
I have a table of 100 tickets. the status of a ticket can be open/closed/canceled. It is the duty of 5 people (say A,B,C,D,E) to constantly deal with the tickets.

Person A jumps to page 5 (showing 20 open tickets at a time) and is working on 1st ticket. In the meantime, the rest 4 people, close all the other tickets on page 5. When person A closes the ticket and returns back on page 5, he sees no rows ..

I have written my sql query in the fashion shown in

http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

except that my SELECT statement inside the {} of WITH statement is complicated.

The behavior that I want is that if the stored procedure is passed @pageNum = 5, and there are only 3 pages of records left then it should return the last page (3rd page) with however many records(0-20) are on that page.

I have not been able to find any solutions on the web and even in this forum regarding this. any help will be appreciated

thx
moskhan2

 

[danvlas]

declare a variable
Check the number of pages
Set the variable to the minimum between the calculated number of pages and the @pagenum
Pass the variable instead of the parameter.


Daniel Vlas
Systems Consultant

 

[moskhan2]

danvlas,

In order to check the number of pages, I would have to run the 'SELECT COUNT(*)...' query to find out what the total number of records are that match my criteria. If possible I would like to avoid duplicating the query within the WITH {} statement since that is a complex query and apart from duplication of that code, twice the amount of work will be involved.

The only other options I see are
1) Create a temp table to store prim keys of those records that match the criteria and then get the proper page(if it exists, else calculate the last page and get that page). This would involve creating a temp table every time the stored procedure is called.

2) The second option is what to rely on the inherent speed of the db and loop over each record (checking to see if it satisfies my criteria) and store it in an array of size 20, once I have filled the array and I am at the desired page, return. Otherwise continue looping over the next few records till there are no more records left.

Is there a better method that I do not know of ? or should I use one of the 2 mentioned above ?