Re: How To Return A "Range Of Rows"??

On Apr 18, 11:23 am, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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


Hugo -

Good catch! I never would have found that. That was spot on - I am
sure
the author of the article will appreciate the correction. So, I got
these results
with the adjustment (the formatting is sloppy but the lines at the
bottom are supposed to be under each column title from the query's
results):

CurrentPage PageSize TotalPages TotalRows UpperBoundary
LowerBoundary
----------- ----------- ----------- ----------- -------------
-------------
1 10 0 0 10 1

id
bday_day
bday_month
bday_year
gender
zipCode
siteId userID
photo_location
photo_name photo_default
no_photo headline
about_me
login_date login_isonline
up_order saved_orderby
saved_sort
saved_fage
saved_tage
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
-------------------------------------------------- -----------
----------------------------------------------------------------------------------------------------
-------------------------------------------------- -------------
-------- --------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ --------------
----------- -------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------


@RETURN_VALUE = 0


MY final question is - why don't I get any data returned with the
query? Shouldn't I be seeing results with the column headers? Or, is
this just to show that the query works in debug mode? Sorry - still
trying to learn my way around stored procedures.

Thanks again for your help.
Peter

.