|
|
Posted by pbd22 on 06/12/07 02:21
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
Thanks Erland.
I am too pooped to digest your comments tonight. I'll try a read
before work tomorrow. Thanks for responding. Much appreciated!
Navigation:
[Reply to this message]
|