Posted by pbd22 on 07/13/07 19:17

On Jun 13, 2:52 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> pbd22 (dush...@gmail.com) writes:
> > 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) :
>
> That query looks very much like the SELECT in the procedure you
> posted?
>
> >> 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.
>
> The particular query I asked about was:
>
>
>
> > 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
>
> 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.
>
> >> 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.
>
> In such case, replace SET ROWCOUNT with SELECT TOP(@rowsize).
>
> --
> 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

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:

> 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.

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

[Back to original message]