|
|
Posted by pbd22 on 04/18/07 18:57
On Apr 18, 11:23 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> 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
[Back to original message]
|