|
|
Posted by Hugo Kornelis on 04/18/07 18:23
On 18 Apr 2007 09:10:16 -0700, pbd22 wrote:
(snip)
>Thanks for your reply. I have made the changes you suggested and got
>the following output:
>
>CurrentPage PageSize TotalPages TotalRows UpperBoundary
>LowerBoundary
>-----------........ ----------- ...... ----------- .....
>----------- ........... ------------- .......-------------
>0 ........ 10 .......... 0 .....
>0 ............ 0 ....... -9
>
>
>
>Server: Msg 507, Level 16, State 2, Procedure sp_PeopleSearch, Line 63
>[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
>SET ROWCOUNT. Must be a non-null non-negative integer.
Hi pbd22,
Thanks. This makes a lot clear.
Obviously, there are no rows returned by your query. Or to be more
precies - by the first copy of your query. You will have noted that you
have multiple copies of the same query in the procedure; the first copy
uses "SELECT @rows = COUNT(*), @pages = COUNT(*) / @perpage" to find the
total number of rows in the query. Since @rows is only set here, and it
is shown to be 0 in the results above, the query obviously returns no
rows. That means that either the query you want to run in a paged mode
does in fact not return any rows, or that you made a mistake when making
the extra copies of this query.
After this row-counting, some calculations are done:
(1) IF @rows % @perpage != 0 SET @pages = @pages + 1
(2) IF @pagenum < 1 SET @pagenum = 1
(3) IF @pagenum > @pages SET @pagenum = @pages
(4) SET @ubound = @perpage * @pagenum
(5) SET @lbound = @ubound - (@perpage - 1)
Line 1: The value of @perpage is rounded down in the query; this line
corrects that to rounding up. I'd have done that a little differently,
but that's mostly a matter of taste, and it works.
Line 2: In case someone passed a page number of zero or less to the
procedure, set it to 1. This is to prevent erroneous results.
Line 3: Another sanity check - if the page number passed to the proc
exceeds the actual number of pages, set it to the highest actual page
number. In this case, with 0 rows, this sets the page number to 0;
exactly the value that line 2 attempts to prevent!! This is the source
of the error you received.
Lines 4 and 5: Based on the page number, calculate the number of the
first and the last row to display. For page number 1, these numbers
would have been 1 and 10. For page number 0, the calculation yields -9
and 0. As a result, the proc later tries to set a negative ROWCOUNT.
I _think_ that you can easily fix this by swapping lines 2 and 3. I have
not checked every bit of the proc, but I suggest that you simply try it
and see what happens. If this results in (different) problems, you'll
have to explicitly add code to handle the possibility of an empty result
set.
I have already sent a mail to the author of the aspfaq article I refered
you to earlier, asking him to correct this page.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|