Re: Establishing Precedence In ORDERBY Condition Causing Problems.

Hi Erland,

OK. thanks for your reply.

Well, I am guessing you are confused from the "different results based
on
different users" nature of my question vs. what the SPROC is telling
you
because the way I have it set up is that the acutual saved query is
stored
as a string in a database table. The string gets saved and called at a
later
time when the user wants to use that particular search. An example
stored
search looks like the below (sorry for the code dump, but its for
illustration) :

select coalesce (tab1.emailAddress, tab2.user_name,
tab3.email_address, tab4.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 from ( select distinct emailAddress from Users union
select distinct user_name from PersonalPhotos union select distinct
email_address from EditProfile union select distinct email_address
from LastLogin ) drv Left Join Users tab1 on (drv.emailAddress =
tab1.emailAddress) Left Join PersonalPhotos tab2 on (drv.emailAddress
= tab2.user_name) Left Join LastLogin tab4 on (drv.emailAddress =
tab4.email_address) Left Join EditProfile tab3 on (drv.emailAddress =
tab3.email_address) where tab2.photo_default = 1 and tab2.no_photo = 1
order by tab1.registerDate ;

This method has been working for me except for when the WHERE clasuse
is
describing a zipcode search. In this case the ORDERBY conditions need
to describe each individual user and can be quite long. And, when an
individual user deletes his profile, a whole saved search can fail.
So, I created the UserPrecedence table that describes the ordered
list. Users can be deleted from the UserPrecedence table when they
remove themselves from the system.

So, now, ORDERBY registerDate, login_date, edit_date, or the zipcode
CASE statement is now handled by:

ORDER BY CASE @sort

where @sort represents 1,2,3, or 4 corresponding to each of the above
conditions.

You haven't assigned @sort yet, so what does it do in the ORDER BY
clause? And why do you have the same WHERE clause here as when you
do the count and return the data. What is this supposed to achieve?

The ORDER BY CASE @sort is supposed to only tell SQL to return data
based on the user's prefer'd search condition (registerDate,
edit_date, etc) and do it once. Since I have made the UserPrecedence
addition and attempted to figure out how to add paging to my results,
I have made a number of changes to my procedure and am no longer
getting predictable/reliable results (when I get results at all). If
you see some obvious errors, I'd appreciate change suggestions as
I am a bit over my head at this point.

By the way, which version of SQL Server are you using?

I was SQL Server 2000 when I wrote this SPROC but we have since
upgraded to SQL Express.

I hope I have answered your questions. Let me know if you have others.
I appreciate your help/suggestions.

Regards,
Peter

On Jun 11, 3:15 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
pbd22 (dush...@xxxxxxxxx) writes:
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?

I don't know your tables, but the procedure looks funny. From your
description it sounds like the query would return different results
depening on who is running it, or at least in different order, but
I can't work out how that should happen.

A few more comments:

ALTER PROCEDURE [dbo].[sp_PeopleSearch]

The sp_ prefix is reserved for system procedures, and SQL Server first
looks for these procedures in master. Don't use it for your own code.

(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)

There is a left join followed by an inner join, which refers back to
the table in the left join. If the first LEFT JOIN is there for a
reason, you convert it to an inner join here.



SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string
...
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

You haven't assigned @sort yet, so what does do in the ORDER BY
clause. And why do you have the same WHERE clause here is when you
do the count and return the data. What is this supposed to achieve?

By the way, which version of SQL Server are you using?

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.