How To Return A "Range Of Rows"??

[pbd22]

Hi.

How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?

thanks

[Greg D. Moore \(Strider\)]

"pbd22" <du*****@gmail.comwrote in message
news:11*********************@y5g2000hsa.googlegrou ps.com...

Hi.

How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?

Depends partly on which version of SQL Server you're using.

2000 or 2005?

2000, you need something like

select top 5 orderid from dbo.orders where orderid in
(select top 10 orderid from dbo.orders order by orderid) order by orderid
desc

for 2005 something like:
with OrdersCTE as
(
select row_number() over (order by orderid) as rownum, *
from dbo.orders
)

select * from OrdersCTE where rownum between 5 and 10

(obviously don't use * in your actual production code)

>
thanks

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[pbd22]

On Apr 12, 8:55 am, "pbd22" <[e-mail]:

Hi.

How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?

thanks

Hi.

I am following up my own message with more detailed info.
If some """kind""" soul could tell me how to isolate a range
of rows for each of the following conditions I would really
(**really**)
apprecaite it. The SQL is dynamically generated based on user
requirements. The resulting statements vary wildly but, the below
three
look more-or-less like the rest of them:

I REALLY appreciate your help. Thanks!

-----------------------------------------------------------------------------------------------------------------

EXAMPLE ONE (ORDERBY CASE STATEMENT):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from
( select distinct postalAddress from Profiles union select distinct
name from ProfilesPictures union select distinct postaladdress from
ChangeSettings union select distinct postaladdress from LastSignIn )
drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.sex='men' and
tab1.bdayyear between '0' and '1988' and tab2.photodefault = 1 or
tab2.nophoto = 1 order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100
THEN 14 WHEN userID=96 THEN 15 WHEN userID=70 THEN 16 WHEN userID=98
THEN 17 WHEN userID=69 THEN 18 WHEN userID=92 THEN 19 WHEN userID=93
THEN 20 WHEN userID=68 THEN 21 WHEN userID=65 THEN 22 WHEN userID=86
THEN 23 WHEN userID=91 THEN 24 WHEN userID=80 THEN 25 WHEN userID=76
THEN 26 WHEN userID=73 THEN 27 WHEN userID=72 THEN 28 WHEN userID=74
THEN 29 WHEN userID=83 THEN 30 WHEN userID=84 THEN 31 WHEN userID=88
THEN 32 WHEN userID=75 THEN 33 WHEN userID=77 THEN 34 WHEN userID=78
THEN 35 WHEN userID=79 THEN 36 WHEN userID=61 THEN 37 WHEN userID=101
THEN 38 WHEN userID=97 THEN 39 WHEN userID=99 THEN 40 WHEN userID=95
THEN 41 WHEN userID=71 THEN 42 WHEN userID=85 THEN 43 WHEN userID=62
THEN 44 END ;

EXAMPLE TWO (ORDERBY "SOME COLUMN"... COULD BE DATE, TIME, NUMBER,
ETC. THIS IS ALWAYS ASCENDING):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between '0' and '1988' and tab2.photodefault = 1 or tab2.nophoto = 1
order by tab1.registerDate ;

EXAMPLE THREE (ORDERBY LAST TIME LOGGED IN - ALWAYS DESCENDING):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between '0' and '1989' and tab2.photodefault = 1 and not tab2.nophoto
= 1 order by tab4.signindate DESC ;

[Hugo Kornelis]

On 12 Apr 2007 11:46:57 -0700, pbd22 wrote:

>I am following up my own message with more detailed info.
If some """kind""" soul could tell me how to isolate a range
of rows for each of the following conditions I would really
(**really**)
apprecaite it.

Hi pbd22,

Some kind sould has even written a whole web page full of methods to
achieve this. The first part of the page focuses on ASP techniques, but
the second part covers DB techniques for paging.

http://databases.aspfaq.com/database...recordset.html

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[pbd22]

On Apr 12, 11:46 am, "pbd22" <[e-mail]:

On Apr 12, 8:55 am, "pbd22" <[e-mail]:

Hi.

How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?

thanks

Hi.

I am following up my own message with more detailed info.
If some """kind""" soul could tell me how to isolate a range
of rows for each of the following conditions I would really
(**really**)
apprecaite it. The SQL is dynamically generated based on user
requirements. The resulting statements vary wildly but, the below
three
look more-or-less like the rest of them:

I REALLY appreciate your help. Thanks!

-----------------------------------------------------------------------------------------------------------------

EXAMPLE ONE (ORDERBY CASE STATEMENT):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from
( select distinct postalAddress from Profiles union select distinct
name from ProfilesPictures union select distinct postaladdress from
ChangeSettings union select distinct postaladdress from LastSignIn )
drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.sex='men' and
tab1.bdayyear between '0' and '1988' and tab2.photodefault = 1 or
tab2.nophoto = 1 order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100
THEN 14 WHEN userID=96 THEN 15 WHEN userID=70 THEN 16 WHEN userID=98
THEN 17 WHEN userID=69 THEN 18 WHEN userID=92 THEN 19 WHEN userID=93
THEN 20 WHEN userID=68 THEN 21 WHEN userID=65 THEN 22 WHEN userID=86
THEN 23 WHEN userID=91 THEN 24 WHEN userID=80 THEN 25 WHEN userID=76
THEN 26 WHEN userID=73 THEN 27 WHEN userID=72 THEN 28 WHEN userID=74
THEN 29 WHEN userID=83 THEN 30 WHEN userID=84 THEN 31 WHEN userID=88
THEN 32 WHEN userID=75 THEN 33 WHEN userID=77 THEN 34 WHEN userID=78
THEN 35 WHEN userID=79 THEN 36 WHEN userID=61 THEN 37 WHEN userID=101
THEN 38 WHEN userID=97 THEN 39 WHEN userID=99 THEN 40 WHEN userID=95
THEN 41 WHEN userID=71 THEN 42 WHEN userID=85 THEN 43 WHEN userID=62
THEN 44 END ;

EXAMPLE TWO (ORDERBY "SOME COLUMN"... COULD BE DATE, TIME, NUMBER,
ETC. THIS IS ALWAYS ASCENDING):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between '0' and '1988' and tab2.photodefault = 1 or tab2.nophoto = 1
order by tab1.registerDate ;

EXAMPLE THREE (ORDERBY LAST TIME LOGGED IN - ALWAYS DESCENDING):

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between '0' and '1989' and tab2.photodefault = 1 and not tab2.nophoto
= 1 order by tab4.signindate DESC ;

Thanks Greg!

I "am" using 2005 and the solution looks much easier than the 2000
alternatives.
I am not sure why, but the second message I posted in this thread was
right after the first but it appeared after your response. Regardless,
as you can see my queries are somewhat complicated and I am not quite
sure how to apply your 2005 solution (OrdersCTE) to them. I am
wondering if you could use one of my queries to demonstrate how it
works (I hope I am not being too demanding)?
It would help me get my mind wrapped around things.

Thanks kindly for your time.

Peter

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

I "am" using 2005 and the solution looks much easier than the 2000
alternatives.
I am not sure why, but the second message I posted in this thread was
right after the first but it appeared after your response. Regardless,
as you can see my queries are somewhat complicated and I am not quite
sure how to apply your 2005 solution (OrdersCTE) to them. I am
wondering if you could use one of my queries to demonstrate how it
works (I hope I am not being too demanding)?
It would help me get my mind wrapped around things.

WITH CTE (
SELECT <yourselectlistgoeshere>,
rownum = row_number() over( ORDER BY <yourorderbylisthere>)
FROM <yourfrom+joinshere>
WHERE <anywhereclauseyoumayhav>
)
SELECT <yourselectlisthereagain, nowwithoutaliases>
FROM CTE
WHERE rownum BETWEEN @first AND @last
ORDER BY rownum
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pbd22]

On Apr 12, 1:38 pm, Hugo Kornelis
<[e-mail]:

On 12 Apr 2007 11:46:57 -0700, pbd22 wrote:

I am following up my own message with more detailed info.
If some """kind""" soul could tell me how to isolate a range
of rows for each of the following conditions I would really
(**really**)
apprecaite it.

Hi pbd22,

Some kind sould has even written a whole web page full of methods to
achieve this. The first part of the page focuses on ASP techniques, but
the second part covers DB techniques for paging.

http://databases.aspfaq.com/database...ugh-a-recordse...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thanks Hugo (horay for kind souls).
I will go through this in detail after I get home. But, for now,
I am wondering if it is more cost-effective to do the paging logic
on the client or the server? I have already written all my paging
logic
in javascript (basically, what that link provides, but in javascript).
Do
I save on performance by moving the paging away from the server
or do you think it matters?

Thanks again.

[Greg D. Moore \(Strider\)]

"pbd22" <du*****@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...

>
Thanks Hugo (horay for kind souls).
I will go through this in detail after I get home. But, for now,
I am wondering if it is more cost-effective to do the paging logic
on the client or the server? I have already written all my paging
logic
in javascript (basically, what that link provides, but in javascript).
Do
I save on performance by moving the paging away from the server
or do you think it matters?

Depends, how many rows are you returning to the client?

You generally want to return as little data to the client as necessary. Of
course "necessary" changes for every application.

>
Thanks again.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[Ed Murphy]

pbd22 wrote:

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from

Why the hell doesn't anyone format their queries for readability?

order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100

This should be driven by a Users table. Do you really want to mess
around with altering the query every time a user is added/removed?

[Erland Sommarskog]

pbd22 (du*****@gmail.com) writes:

I will go through this in detail after I get home. But, for now,
I am wondering if it is more cost-effective to do the paging logic
on the client or the server? I have already written all my paging
logic in javascript (basically, what that link provides, but in
javascript). Do I save on performance by moving the paging away from the
server or do you think it matters?

Disclaimer: I have no personal experience of writing web apps, so take this
as a grain of salt.

If you can be sure that the search can never return more than, say, 1000
rows (and this can be acieved by using TOP), reading all rows in one go,
and then page from the web server is likely to be better, since else
there would be an access to the database each time the user presses next.
But if the search could hit tens of thousands of rows, you need to have
some sort of batching mechanism, because if 100 users do that at the same
time, your web server will choke.

And you should not send all rows at once to the browser, unless the user
requests to see all rows. If the browser is on a slow connection, that
can be painful.

Finally: don't forget to give the user the option to see at least 100 items
at once. I hate sites where I only get a spoonful at a time.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pbd22]

On Apr 12, 6:50 pm, Ed Murphy <[e-mail]:

pbd22 wrote:

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from

Why the hell doesn't anyone format their queries for readability?

order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100

This should be driven by a Users table. Do you really want to mess
around with altering the query every time a user is added/removed?

good point Ed. I didn't think of that. I'll obviously have to do that
- yet
another thing on the "to-do list". thanks...

[pbd22]

On Apr 13, 4:18 pm, "pbd22" <[e-mail]:

On Apr 12, 6:50 pm, Ed Murphy <[e-mail]:

pbd22 wrote:

select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from

Why the hell doesn't anyone format their queries for readability?

order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100

This should be driven by a Users table. Do you really want to mess
around with altering the query every time a user is added/removed?

good point Ed. I didn't think of that. I'll obviously have to do that
- yet
another thing on the "to-do list". thanks...

Hi All.

So, I have taken all of your advice and turned my code into a stored
procedure that has a dynamic ORDER BY clause for user deletions.
This is turning into a bit of a beast and I am getting well out of my
comfort zone.

I have taken Hugo's advice and moved paging from the client to the
server (or, at least, tried to). I have based my sproc on the RowCount
code in the link he provided. I have also taken Ed's advice and made
my ORDER BY clause table-based for the CASE logic.

I have pasted the code below (sorry if i offend anybody about
formatting.
I am not sure how to format code here... suggestions?). When I use
Query Analyzer and step through the code in "debug" mode, the compiler
jumps to the following line:

----------------------------
SET ROWCOUNT 0
----------------------------

and throws this error:

---------------------------------------------------------------------------------------------

CurrentPage TotalPages TotalRows
----------- ----------- -----------
0 0 0

Server: Msg 507, Level 16, State 2, Procedure sp_SearchRowCount, Line
60
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
SET ROWCOUNT. Must be a non-null non-negative integer.

@RETURN_VALUE = -6
---------------------------------------------------------------------------------------------

Could somebody tell me what about the logic in my code is causing this
failure (I know that the value is null and that that is causing the
error). I have a feeling it might have to do with the following lines
of code that I have commented out in the WHERE clause (but, I am not
sure what it is doing or how to add the logic to my own code):

-- ArtistName + '~' + Title
-- >= @aname + '~' + @title
Other feedback appreciated also. I really appreciate your help!
Thanks.

---------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[sp_SearchRowCount]
@pagenum INT = 1,
@perpage INT = 10
AS
BEGIN
SET NOCOUNT ON

DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT

SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from Photos with(nolock) union select distinct email_address
from Edit with(nolock) union select distinct email_address
from Searches with(nolock) union select distinct email_address
from Precedence with(nolock) union select distinct
email_address
from LoginDate with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress =
tab1.emailAddress)
Inner Join Precedence tab5 on tab5.UserID=tab1.UserID
Left Join Photos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LoginDate tab4 on (drv.emailAddress =
tab4.email_address)
Left Join Edit tab3 on (drv.emailAddress = tab3.email_address)
Left Join Searches tab6 on (drv.emailAddress =
tab6.email_address)

IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum < 1 SET @pagenum = 1
IF @pagenum @pages SET @pagenum = @pages

SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)

SELECT

CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows

-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows

DECLARE

@gender VARCHAR(50),
@country VARCHAR(50),
@orderby INTEGER,
@low VARCHAR(50),
@high VARCHAR(50),
@photo VARCHAR(50),
@sort INTEGER

SET ROWCOUNT @lbound

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from Photos with(nolock) union select distinct email_address
from Edit with(nolock) union select distinct email_address
from Searches with(nolock) union select distinct email_address
from Precedence with(nolock) union select distinct
email_address
from LoginDate with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress =
tab1.emailAddress)
Inner Join Precedence tab5 on tab5.UserID=tab1.UserID
Left Join Photos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LoginDate tab4 on (drv.emailAddress =
tab4.email_address)
Left Join Edit tab3 on (drv.emailAddress = tab3.email_address)
Left Join Searches tab6 on (drv.emailAddress =
tab6.email_address)

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT @perPage

SELECT COALESCE
(
tab1.emailAddress,
tab2.user_name,
tab3.email_address,
tab4.email_address,
tab5.email_address,
tab6.email_address
)
id ,
tab1.bday_day ,
tab1.bday_month ,
tab1.bday_year ,
tab1.gender ,
tab1.zipCode ,
tab1.siteId ,
tab1.userID ,
tab2.photo_location ,
tab2.photo_name ,
tab2.photo_default ,
tab2.no_photo ,
tab3.headline ,
tab3.about_me ,
tab4.login_date ,
tab4.login_isonline,
tab5.up_order,
tab6.saved_orderby,
tab6.saved_sort,
tab6.saved_fage,
tab6.saved_tage

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from Photos with(nolock) union select distinct email_address
from Edit with(nolock) union select distinct email_address
from Searches with(nolock) union select distinct email_address
from Precedence with(nolock) union select distinct
email_address
from LoginDate with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress =
tab1.emailAddress)
Inner Join Precedence tab5 on tab5.UserID=tab1.UserID
Left Join Photos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LoginDate tab4 on (drv.emailAddress =
tab4.email_address)
Left Join Edit tab3 on (drv.emailAddress = tab3.email_address)
Left Join Searches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE

tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo

-- ArtistName + '~' + Title
-- >= @aname + '~' + @title

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT 0

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[Hugo Kornelis]

On 18 Apr 2007 06:12:44 -0700, pbd22 wrote:

>I have pasted the code below (sorry if i offend anybody about
formatting.
I am not sure how to format code here... suggestions?). When I use
Query Analyzer and step through the code in "debug" mode, the compiler
jumps to the following line:

----------------------------
SET ROWCOUNT 0
----------------------------

and throws this error:

---------------------------------------------------------------------------------------------

CurrentPage TotalPages TotalRows
----------- ----------- -----------
0 0 0

Server: Msg 507, Level 16, State 2, Procedure sp_SearchRowCount, Line
60
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
SET ROWCOUNT. Must be a non-null non-negative integer.

@RETURN_VALUE = -6
---------------------------------------------------------------------------------------------

Could somebody tell me what about the logic in my code is causing this
failure (I know that the value is null and that that is causing the
error). I have a feeling it might have to do with the following lines
of code that I have commented out in the WHERE clause (but, I am not
sure what it is doing or how to add the logic to my own code):

Hi pbd22,

Since those commented lines are *AFTER* the place where the error occurs
(the debugger jumps to the incorrect line; the only line that can cause
this error is either this one:

SET ROWCOUNT @lbound

or this one:

SET ROWCOUNT @perPage

I assume that it's the first, and that it's cause by some unexpected
things going on in the calculations neede to compute @lbound.

You already have added a great statement to aid in debugging; I'm gonna
ask you to add a bit more to it - so please change this:

SELECT

CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows

to this:

SELECT

CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound

And then rerun the query and post back the results. We'll then have to
take it from there.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[pbd22]

On Apr 18, 8:08 am, Hugo Kornelis
<[e-mail]:

On 18 Apr 2007 06:12:44 -0700, pbd22 wrote:

I have pasted the code below (sorry if i offend anybody about
formatting.
I am not sure how to format code here... suggestions?). When I use
Query Analyzer and step through the code in "debug" mode, the compiler
jumps to the following line:

----------------------------
SET ROWCOUNT 0
----------------------------

and throws this error:

---------------------------------------------------------------------------------------------

CurrentPage TotalPages TotalRows
----------- ----------- -----------
0 0 0

Server: Msg 507, Level 16, State 2, Procedure sp_SearchRowCount, Line
60
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid argument for
SET ROWCOUNT. Must be a non-null non-negative integer.

@RETURN_VALUE = -6
---------------------------------------------------------------------------------------------

Could somebody tell me what about the logic in my code is causing this
failure (I know that the value is null and that that is causing the
error). I have a feeling it might have to do with the following lines
of code that I have commented out in the WHERE clause (but, I am not
sure what it is doing or how to add the logic to my own code):

Hi pbd22,

Since those commented lines are *AFTER* the place where the error occurs
(the debugger jumps to the incorrect line; the only line that can cause
this error is either this one:

SET ROWCOUNT @lbound

or this one:

SET ROWCOUNT @perPage

I assume that it's the first, and that it's cause by some unexpected
things going on in the calculations neede to compute @lbound.

You already have added a great statement to aid in debugging; I'm gonna
ask you to add a bit more to it - so please change this:

SELECT

CurrentPage = @pagenum,
TotalPages = @pages,
TotalRows = @rows

to this:

SELECT

CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound

And then rerun the query and post back the results. We'll then have to
take it from there.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Hi Hugo,

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.

[Hugo Kornelis]

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

[pbd22]

On Apr 18, 11:23 am, Hugo Kornelis
<[e-mail]:

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

[Hugo Kornelis]

On 18 Apr 2007 11:57:53 -0700, pbd22 wrote:

(snip)

>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.

Hi Peter,

You're probably going to hate me for this, but my best guess is that
your tables simply don't contain any data that matches the criteria in
the trigger. I already hinted at this in my previous reply (though I
left a back door open for the possibilty that one of the queries
contained a copy/paste error).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[pbd22]

Ah, yes. You were right Hugo. It was a problem with my select
statement.

Thanks again for your help, MUCH appreciated!

Cheers.
Peter

Hugo Kornelis wote:

On 18 Apr 2007 11:57:53 -0700, pbd22 wrote:

(snip)

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.

Hi Peter,

You're probably going to hate me for this, but my best guess is that
your tables simply don't contain any data that matches the criteria in
the trigger. I already hinted at this in my previous reply (though I
left a back door open for the possibilty that one of the queries
contained a copy/paste error).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis