Establishing Precedence In ORDERBY Condition Causing Problems.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pbd22 - 11 Jun 2007 15:16 GMT

Hi.

I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.

My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.

I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.

Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.

I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?

As always, much appreciated.

PS - In addition to the UserPrecedence change, I have attempted to add
paging - returning N amount of pages per request based on passed-in
paramaters. I'd appreciate it if you could take a quick glance here
also just to make sure my logic is OK.

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

ALTER PROCEDURE [dbo].[sp_PeopleSearch]
@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 CustomerPhotos with(nolock) union select distinct email_address
    from EditProfile with(nolock) union select distinct email_address
    from SavedSearches with(nolock) union select distinct email_address
    from UserPrecedence with(nolock) union select distinct email_address
    from RecentLogin with(nolock)) drv
    Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
    Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
    Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
    Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
    Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
    Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

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

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

SELECT

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

-- 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 CustomerPhotos with(nolock) union select distinct email_address
    from EditProfile with(nolock) union select distinct email_address
    from SavedSearches with(nolock) union select distinct email_address
    from UserPrecedence with(nolock) union select distinct email_address
    from RecentLogin with(nolock)) drv
    Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
    Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
    Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
    Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
    Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
    Left Join SavedSearches 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 CustomerPhotos with(nolock) union select distinct email_address
    from EditProfile with(nolock) union select distinct email_address
    from SavedSearches with(nolock) union select distinct email_address
    from UserPrecedence with(nolock) union select distinct email_address
    from RecentLogin with(nolock)) drv
    Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
    Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
    Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
    Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
    Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
    Left Join SavedSearches 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

    --and not tab2.no_photo = 1
    --firstName + '~' + lastName
-->= @fname + '~' + @lname

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