Re: Establishing Precedence In ORDERBY Condition Causing Problems.

pbd22 (dushkin@xxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

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
.