By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

Parameterized order by clause: doesn't work

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.