|
|
Posted by Erland Sommarskog on 06/13/07 21:52
pbd22 (dushkin@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, 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]
|