|
|
Posted by Erland Sommarskog on 06/11/07 22:15
pbd22 (dushkin@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|