Help - Sorting Tables Returned By Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help - Sorting Tables Returned By Stored Procedure

Author

Topic

overbored
Starting Member

12 Posts

Posted - 2002-01-18 : 21:36:24

How do you go about sorting the data that a stored procedure selects? Or an even broader question is: how do you do *anything* with a table that a stored procedure selects? Please tell me this is possible!

FYI I've tried:

- stored_proc params ORDER BY blah
- EXECUTE stored_proc params ORDER BY blah

- (stored_proc params) ORDER BY blah
- (EXECUTE stored_proc params) ORDER BY blah

- SELECT stored_proc params ORDER BY blah
- SELECT EXECUTE stored_proc params ORDER BY blah

- SELECT (stored_proc params) ORDER BY blah
- SELECT (EXECUTE stored_proc params) ORDER BY blah

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-18 : 22:11:04

You can do an order by on a SELECT statement, not on an EXEC or stored procedure. You can do an Order by IN a Stored Procedure.

If you want dynamic order by clauses I'd look here: http://www.sqlteam.com/item.asp?ItemID=2209

Or you can write dynamic SQL: http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic+sql

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

overbored
Starting Member

12 Posts

Posted - 2002-01-18 : 22:45:30

This will work for me, but I can't get descending sort to work. Whenever I add a DESC to the end of the sort column name in the CASE statement, T-SQL tells me there's a syntax error. I tried putting the result expression in quotes, but to no avail. In fact, I think it would only work with one-word result expression. Any tips?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-18 : 22:48:34

you can do

select *
from tbl
order by col1 desc, col2 desc

but not

select *
from tbl
order by case @i when 1 then col1 desc else col2 desc end

to do this you need something like

select *
from tbl
order by
case @i when 1 then col1 else 1 end desc ,
case @i when 2 then col2 else 1 end desc

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/18/2002 22:49:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources