Tim Bunce writes:
> On Tue, Aug 07, 2001 at 04:33:48PM -0700, Michael Peppler wrote:
> > I've done that, and it works just fine, provided you have a primary
> > key that lends itself to this sort of thing...
>
> Got any code you can share?
Well - you know me... it's 99% stored procedure code, but here's an
example that lists members of a group in alphabetical order, even
though the primary key is really on the userId. This proc does not do
reverse paging, but that wouldn't be hard to implement (I've done it
in other procs, but they can get quite big...)
The call sequence for this proc is:
exec listGroupMembers @userId = 1234, @circleId = 3456
[, @lastFamilyName = '....'
, @lastGivenName = '...'
, @lastId = ...
]
, @rows = 100
and the proc returns up to @rows rows, starting at
lastFamilyName/lastGivenName if they are passed in.
The perl side then simply needs to remember the last userId,
familyName and givenName values and pass those in to the call when the
user wants to page forward.
This is obviously Sybase (or MS-SQL) specific code, but you can
certainly use the basic SQL query in another situation.
Michael
create proc listCombinedCircleMembers
@userId numeric(9,0)
, @circleId numeric(9,0) = NULL
, @lastFamilyName varchar(32) = NULL
, @lastGivenName varchar(32) = NULL
, @lastId numeric(9,0) = NULL
, @rows int = 100
as
declare @lastFName varchar(32)
, @lastGName varchar(32)
, @id numeric(9,0)
if @lastFamilyName != NULL and @lastGivenName != NULL and @lastId != NULL
begin
-- select 'DEBUG fn' = @lastFamilyName
select @lastFName = @lastFamilyName
, @lastGName = @lastGivenName
, @id = @lastId
end
else
begin
-- select 'DEBUG no name' = @lastFamilyName
select @lastFName = " "
, @lastGName = " "
, @id = 0
end
-- select 'DEBUG lastF' = @lastFName
-- , 'lastG' = @lastGName
-- , 'lastI' = @id
begin
set rowcount @rows
select distinct u.userId
, u.givenName
, u.familyName
, u.primaryEmail
, 'affCode' = isnull(af.affCode, 'ec')
, 'template' = isnull(af.template, 'ec')
, 'homeURL' = isnull(af.homeURL, 'www.eCircles.com')
, 'affName' = isnull(af.affName, 'eCircles')
from eC_user u, eC_circleMember cm, eC_circle c, eC_circleMember cm2
, eC_affiliate af
where cm.userId = u.userId
and isnull(cm.userType, 0) = 0
and u.userId != @userId
and (cm.userId != @userId or isnull(cm.userType, 0) != cm.userType)
and cm.circleId = c.circleId
and c.status < 4
and cm.circleId = cm2.circleId
and cm2.userId = @userId
and c.circleId != @circleId
and u.userId not in (select userId from eC_circleMember
where circleId = @circleId)
and u.oem *= af.affCode
and ((u.familyName = @lastFName and u.givenName = @lastGName and u.userId >
@id)
or (u.familyName = @lastFName and u.givenName > @lastGName and u.userId !=
@id)
or (u.familyName > @lastFName and u.givenName != @lastGName and u.userId
!= @id) )
order by u.familyName
, u.givenName
, u.userId
set rowcount 0
end
