Parameterized order by clause: doesn't work

[JJ_377]

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

[Hugo Kornelis]

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)

[JJ_377]

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)

[JJ_377]

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)

[Hugo Kornelis]

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)

[JJ_377]

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

[Hugo Kornelis]

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)

[JJ_377]

hmm...I understand what you are saying...intere sting
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)