473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Parameterized order by clause: doesn't work

Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have no
idea how it is evaluating the order by clause.
THANK YOU!

CREATE proc sprAllBooks

@SortAscend varchar(4),
@SortColumn varchar(10)

as

If @SortAscend = 'DESC'
Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate

from authors
inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id

ORDER BY au_lname
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
DESC

ELSE

Select titles.title_id, title, au_lname, au_fname,
Convert(varchar(12), pubdate, 101) as PubDate

from authors

inner join
titleauthor
on
authors.au_id = titleauthor.au_id
inner join
titles
on
titleauthor.title_id = Titles.title_id

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
GO

Jul 23 '05 #1
7 2727
On 25 Feb 2005 08:39:07 -0800, JJ****@hotmail.com wrote:
Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have no
idea how it is evaluating the order by clause.
THANK YOU!
Hi JJ,

You forgot to tell us how you call the procedure (what values for
@SortAscend and @SortColumn you use) and what results you get.

I tried your code and after fixing a syntax error, it works as I would
expect it to. You apparently expected something else, but what you
expected is not clear from your post.

Some small pointers to what might be your problem:
If @SortAscend = 'DESC' (snip)ORDER BY au_lname
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
DESC


This resulted in an error. You either have to append a comma after ORDER
BY au_lname, or remove au_lname. I figured the latter is what you want,
so that's how I tested it.

The last CASE expression won't refer to the formatted pubdate, but to
the pubdate column in the table. Even though the ORDER BY does allow you
to refer to an alias from the SELECT clause, it does not allow you to do
so in an expression.

The DESC will only apply to the ordering by pubdate. The title and
au_lname are in seperate expressions that require a seperate DESC:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2
Thank you Hugo. Good points. For debugging purposes, I've been calling
the sproc from the debugger with the parameters @SortAscend = 'DESC'
and @SortColumn = 'title. The result set is not sorted by title desc,
as I would expect it should have been. Quite inexplicably (to me), the
result set now seems to be sorting by au_lname desc (!)

Note that the order by clause should only feature the case evaluation
statement (au_lname was there as a left-over from a debugging
attempt...):

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END

I think I see what you are getting at about the PubDate (alias) and
pubdate (table column) name and will give that a try...

Also, you related that one part of the code resulted in an error...can
you tell me how to unearth that? The Sql debugger seemed to be silent
on that point to me...

Thank you again...

J.


Hugo Kornelis wrote:
On 25 Feb 2005 08:39:07 -0800, JJ****@hotmail.com wrote:
Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have no
idea how it is evaluating the order by clause.
THANK YOU!
Hi JJ,

You forgot to tell us how you call the procedure (what values for
@SortAscend and @SortColumn you use) and what results you get.

I tried your code and after fixing a syntax error, it works as I

would expect it to. You apparently expected something else, but what you
expected is not clear from your post.

Some small pointers to what might be your problem:
If @SortAscend = 'DESC' (snip)
ORDER BY au_lname
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
DESC


This resulted in an error. You either have to append a comma after

ORDER BY au_lname, or remove au_lname. I figured the latter is what you want, so that's how I tested it.

The last CASE expression won't refer to the formatted pubdate, but to
the pubdate column in the table. Even though the ORDER BY does allow you to refer to an alias from the SELECT clause, it does not allow you to do so in an expression.

The DESC will only apply to the ordering by pubdate. The title and
au_lname are in seperate expressions that require a seperate DESC:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Jul 23 '05 #3
I figured it out. The order by clause has to look like this:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC

I put the 'DESC' parameter inside each of the case statements and now
my result set is sorting!
JJ****@hotmail.com wrote:
Thank you Hugo. Good points. For debugging purposes, I've been calling the sproc from the debugger with the parameters @SortAscend = 'DESC'
and @SortColumn = 'title. The result set is not sorted by title desc,
as I would expect it should have been. Quite inexplicably (to me), the result set now seems to be sorting by au_lname desc (!)

Note that the order by clause should only feature the case evaluation
statement (au_lname was there as a left-over from a debugging
attempt...):

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END

I think I see what you are getting at about the PubDate (alias) and
pubdate (table column) name and will give that a try...

Also, you related that one part of the code resulted in an error...can you tell me how to unearth that? The Sql debugger seemed to be silent
on that point to me...

Thank you again...

J.


Hugo Kornelis wrote:
On 25 Feb 2005 08:39:07 -0800, JJ****@hotmail.com wrote:
Can someone tell me why SQL seems to ignore my order by clause?
I tried to run through the debugger, but the debugger stops at the
select statement line and then returns the result set; so, I have noidea how it is evaluating the order by clause.
THANK YOU!
Hi JJ,

You forgot to tell us how you call the procedure (what values for
@SortAscend and @SortColumn you use) and what results you get.

I tried your code and after fixing a syntax error, it works as I

would
expect it to. You apparently expected something else, but what you
expected is not clear from your post.

Some small pointers to what might be your problem:
If @SortAscend = 'DESC'

(snip)
ORDER BY au_lname
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END,
CASE @SortColumn WHEN 'PubDate' THEN PubDate END
DESC


This resulted in an error. You either have to append a comma after

ORDER
BY au_lname, or remove au_lname. I figured the latter is what you

want,
so that's how I tested it.

The last CASE expression won't refer to the formatted pubdate, but to the pubdate column in the table. Even though the ORDER BY does

allow you
to refer to an alias from the SELECT clause, it does not allow you
to do
so in an expression.

The DESC will only apply to the ordering by pubdate. The title and
au_lname are in seperate expressions that require a seperate DESC:
ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Jul 23 '05 #4
On 25 Feb 2005 09:15:39 -0800, JJ****@hotmail.com wrote:
Thank you Hugo. Good points. For debugging purposes, I've been calling
the sproc from the debugger with the parameters @SortAscend = 'DESC'
and @SortColumn = 'title. The result set is not sorted by title desc,
as I would expect it should have been. Quite inexplicably (to me), the
result set now seems to be sorting by au_lname desc (!)

Note that the order by clause should only feature the case evaluation
statement (au_lname was there as a left-over from a debugging
attempt...):
Hi J,

I was unable to reproduce this. If I run the query you posted here
(after removing the left-over au_lname) with argument DESC and title, I
get the results in ascending order of title. Not sorted by au_lname.

The only way to get it to sort by descending au_lname is to leave the
left-over au_lname in and remove or comment the three CASE expressions.

Also, you related that one part of the code resulted in an error...can
you tell me how to unearth that? The Sql debugger seemed to be silent
on that point to me...


I'm talking about the code as you posted it here in your original
message (with the left-over au_lname included). I get an error when I
try to create the procedure, or when I try to run that statements by
themselves. The only ways tol solve it are to remove au_lname, to add a
comma at the end of the line or to remove the three case expressions.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #5
Thanks very much again Hugo. All is well now -- with the DESC keywords
within each CASE statement:

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END DESC,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END DESC,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END DESC

I am just starting to use the debugger in SQL and therefore am *very*
interested in learning as much as I can about using it to advantage.
Again, it didn't "complain" about:

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END ,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END
DESC

but, nor did the stored procedure return the intended result set!
I have used QA and the Profiler exclusively to help debug problems.

Jules

Jul 23 '05 #6
On 25 Feb 2005 13:00:22 -0800, JJ****@hotmail.com wrote:

(snip)
Again, it didn't "complain" about:

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END ,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END
DESC

but, nor did the stored procedure return the intended result set!
Hi Jules,

It should not complain about this - it's valid T-SQL syntax. It
specifies that the results should be ordered by three columns:
* first by (depending on the value of @SortColumn) either title or NULL,
ascending (since no direction is specified);
* next by (depending on the value of @SortColumn) either au_lname or
NULL, again ascending;
* and finally, if the previous two didn't suffice to define the sort
order, by (depending on @SortColumn) either pubdate or NULL, but for
this column a descending sort is specified.

I have used QA and the Profiler exclusively to help debug problems.


Those tools are the best (IMO), both for writing and debugging SQL code.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #7
hmm...I understand what you are saying...interesting
it makes sense now - thanks!

Hugo Kornelis wrote:
On 25 Feb 2005 13:00:22 -0800, JJ****@hotmail.com wrote:

(snip)
Again, it didn't "complain" about:

ORDER BY
CASE @SortColumn WHEN 'title' THEN title END,
CASE @SortColumn WHEN 'au_lname' THEN au_lname END ,
CASE @SortColumn WHEN 'PubDate' THEN pubdate END
DESC

but, nor did the stored procedure return the intended result set!
Hi Jules,

It should not complain about this - it's valid T-SQL syntax. It
specifies that the results should be ordered by three columns:
* first by (depending on the value of @SortColumn) either title or

NULL, ascending (since no direction is specified);
* next by (depending on the value of @SortColumn) either au_lname or
NULL, again ascending;
* and finally, if the previous two didn't suffice to define the sort
order, by (depending on @SortColumn) either pubdate or NULL, but for
this column a descending sort is specified.

I have used QA and the Profiler exclusively to help debug problems.
Those tools are the best (IMO), both for writing and debugging SQL

code.
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Peroq | last post by:
Hi all I'm not sure ASP is the problem, but my SQL statement seems fine to me. This works fine : strSQL = "SELECT .* FROM _RechPat INNER JOIN NivPatri ON .cod_niv = NivPatri.cod_niv WHERE...
1
by: Scott | last post by:
I have a view that contains the following SQL1 and it takes about 500ms to run with Explain Plan EXP1. However, if I add an order by clause(e.g. select * from view1 order by ID desc), it will then...
1
by: gary b | last post by:
Hello When I use a PreparedStatement (in jdbc) with the following query: SELECT store_groups_id FROM store_groups WHERE store_groups_id IS NOT NULL AND type = ? ORDER BY group_name
761
by: Neo-LISPer | last post by:
Hey Recently, I researched using C++ for game programming and here is what I found: C++ game developers spend a lot of their time debugging corrupted memory. Few, if any, compilers offer...
8
by: deko | last post by:
I'm trying to open a Recordset based on a parameterized query. I'm kind of new to parameterized queries, so I'm sure I'm missing something simple. Set qdfs = db.QueryDefs Set qdf =...
13
by: bevanward | last post by:
Hi All I am finding unexpected results when inserted into a newly created table that has a field of datatype int identity (1,1). Basically the order I sort on when inserting into the table is...
2
by: rdemyan via AccessMonster.com | last post by:
I can't seem to get the following SQL statement with the ORDER BY clause to work. It does work if I order by any of the fields in the SELECT part of the statement but not if it is the field in the...
8
by: Roland Hall | last post by:
In Access you use "*" + + "*", + can be replaced with & Calling a parameterized query in Access requires % be used in place of *, however, all that I have read show dynamic SQL passed to Access: ...
2
by: Paul Elliott | last post by:
How are parameterized translation strings commonly handled? Suppose I need to create a string like: "file %1 failed to open", where %1 exists at runtime, but I need to create it in a way that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.