Posted by pbd22 on 06/13/07 16:16

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...@sommarskog.se> wrote:
> pbd22 (dush...@gmail.com) 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...@sommarskog.se
>
> 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

[Back to original message]