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

pbd22 (dushkin (AT) gmail (DOT) com) writes:
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:

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.

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.

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 11, 3:15 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland.
I am too pooped to digest your comments tonight. I'll try a read
before work tomorrow. Thanks for responding. Much appreciated!

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.

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.

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... (AT) sommarskog (DOT) se> wrote:

pbd22 (dushkin (AT) gmail (DOT) com) writes:
That query looks very much like the SELECT in the procedure you
posted?

The particular query I asked about was:

The ORDER BY CASE @sort here is meaningless, since at this point @sort
has the value NULL. You answered my question what this CASE @sort was
supposed to achieve by talking about returning data. But you are not
returning data. You are assigning variables.

But the ORDER BY is probably the least strange about this SELECT. As
far as I can call you have <bigquery> thrice in your procedure:

1) SELECT Rows = COUNT(*), Pages = COUNT(*) / @pagesize
FROM <bigquery>

2) SELECT @country = saved_country, @sort = saved_sort, ...
FROM <bigquery>

3) SELECT <cols to client> FROM <bigquery>

1) and 3) makes perfect sense. The second I cannot understand. As far
as I understand, this query is likely to return multiple rows. But which
rows it returns - we don't know. Since @sort is NULL at this point,
the ORDER BY has no effect. It's probably the explanation to why your @sort
goes bad, but I can't say what you should do to correct, because I have very
little clue how your tables are related.

But what I would expect is that you would first read a single row from
the SavedSearches table. But now you seem to include that table in every
query, which seems funny to me - but I very little what this is all about.

In such case, replace SET ROWCOUNT with SELECT TOP(@rowsize).


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 13, 2:52 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hi Erland,

Thanks for your suggestions and apologies for the LONG pause.
I had to focus on another part of development for a bit and am now
back to trying to get my database programming and search design
correct.

To be honest, I know what I did made sense to me at the time, but
since I have tried to add paging and other features to my search
stored
procedure, I think it has gotten away from me.

Per your below comment:

It sounds to me like the middle code block is causing me my errors but
I am
not sure what I am doing wrong still. Would you mind taking a look at
an Entity
Relationship Diagram? It might give you a better understanding of how
my data
is designed and for what purpose. If that is OK, I'll email it to you
via your address
provided here.

I *seriously* appreciate your feedback.

Regards,
Peter

pbd22 (dushkin (AT) gmail (DOT) com) writes:
And I don't know what you are doing wrong, because I don't know what you
are trying to achieve.

There is a common recommendation for this type of questions, and that is
that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The resired result given the sample.

Now, since your original query had some 7-8 tables whereof several repeated
in the FROM clause, you will need to simplify the problem down to the
core.

If I understand this correctly, this is about saved searches, so the
clou is certainly SavedSearches, but try to invent a similar case with
fewer tables. Yes, that may take you some time, but I rather have
you doing that than showing me an E-R diagramme that may not help me
to understand what you are trying to achieve. To wit, I am not sure
that you understand yourself. But if you spend some time with a simpler
case then maybe you get can get that understanding.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jul 13, 3:04 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland.

OK, I have done what you said and reduced the tables used in the
search.
After much messing around with the stored procedure, I have figured
out that
by commenting out the following code (at the end of the procedure), I
can get
results:

WHERE

tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo--WHERE

(and, the ORDERBY code is commented out as it depends on this code).

I have also found that if I leave any one of the above lines the code
again
fails. So, for some reason, @gender, @country, @low, @high, and @photo
are not getting passed appropriately.

This is where I am at the moment, I'll report back as progress is
made.
Comments always appreciated (if you see something I don't) along the
way.

Thanks again for your patience.
Peter

On Jul 15, 6:33 pm, pbd22 <dush... (AT) gmail (DOT) com> wrote:
Hi Erland (or anybody else),

OK. I have changed the procedure significantly to use the Row_Number()
method in SQL 2005 for paging.

In this procedure, I am trying to do the following:

1) used the passed-in parameters to figure out which saved search we
are using.
2) query the SavedSearch table to populate the local parameters with
the saved values
3) create a temporary table that is sorted against the local
paramerters.

I am having problems figuring out how to create this temporary table.
At a quick glance, does the "SELECT COALESCE" statement seem
like it has been logically placed or does it seem out of place? I keep
getting

"Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'. "

I can't seem to build the temp table without errors.
At a quick glance does the logic in this procedure seem to make sense?
Any "trained-eye" corrections would be very helpful.

thanks.



CREATE PROCEDURE tre_SavedSearch
-- passed-in parameters
@searchname VARCHAR(50) = null, -- The Name Of The User-Defined
Search
@emailaddy VARCHAR(50) = null, -- The ID (email) of the User
@PageNum INT = 1, -- The Starting Page
@PageSize INT = 10, -- The Number of Rows Per Page
@debug INT = 0 -- Debug Value

AS
BEGIN

SET NOCOUNT ON

-- first, we need to pull the saved values from the
-- SavedSearch table to understand what we are looking
-- for.

DECLARE
@saveddate VARCHAR(50),
@savedname VARCHAR(50),
@defaultsearch VARCHAR(50),
@sex VARCHAR(50),
@fromage VARCHAR(50),
@toage VARCHAR(50),
@country VARCHAR(50),
@miles VARCHAR(50),
@pictures VARCHAR(50),
@zipcode VARCHAR(50),
@sortID INT -- 1 = registration
-- 2 = recent changes
-- 3 = recent login
-- 4 = distance order

SET @saveddate = (SELECT saved_date FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @savedname = (SELECT saved_name FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @sex = (SELECT saved_sex FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @fromage = (SELECT saved_fage FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @toage = (SELECT saved_tage FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @country = (SELECT saved_country FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @miles = (SELECT saved_miles FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @pictures = (SELECT saved_pictures FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @zipcode = (SELECT saved_postal FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)
SET @sortID = (SELECT saved_sort FROM SavedSearches WHERE
search_name=@searchname AND email_address=@emailaddy)


WITH SavedSearch AS
(

SELECT ROW_NUMBER() OVER ( ORDER BY CASE @sortID

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

) AS RowNum

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

FROM

(select distinct emailAddress
from Users with(nolock) union select distinct user_name
from PersonalPhotos 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 LastLogin with(nolock)) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on (tab5.UserID=tab1.UserID)
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)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE

tab1.gender = @sex
AND tab1.country = @country
AND tab1.bday_year BETWEEN @fromage AND @toage
--AND tab2.photo_default = 1 + @photo

)

)

SELECT * FROM SavedSearch
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize

ORDER BY CASE @sortID

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

END
GO

pbd22 (dushkin (AT) gmail (DOT) com) writes:
This is because you don't have an alias does the derived table. Add
"AS x" before the faulty parenthesis.

Also, you are missing a semi-colon before ';'

I can't even see a temp-table. I can see a common table expression,
is that you are thinking of?

Since you apparently haven't tested the code yet, I don't feel compelled
to make a thorough review. But:

It would be more effecient and less verbose with:

SELECT @saveddate = saved_date, @savedname = saved_name, ...
FROM SavedSearches
WHERE search_name=@searchname
AND email_address=@emailaddy

Really what the CTE that returns a single column is supposed to mean,
I don't know, but I guess that you find out when you test what you
really intended.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx