Stored Procedure: Specifying ASC or DESC sort order through a parameter

Stored Procedure: Specifying ASC or DESC sort order through a parameter

Post by Thomas Line » Thu, 25 Jul 2002 01:07:23



Hi y'all!

I have a little problem that I couldn't seem to find an answer to merely by
searching the web.

I have created a stored procedure in MS SQL Server 2000, and I am trying to
set the sort order to ASC or DESC by an input parameter. I have tried the
different types of CASE sentences after the ORDER BY clause, but I am
getting nowhere.

Specifying an ORDER BY clause in the same manner is an easy task, but not
when it comes to the ASC or DESC clause.

Is there anybody who can help me?

 
 
 

Stored Procedure: Specifying ASC or DESC sort order through a parameter

Post by Anith Se » Thu, 25 Jul 2002 02:26:11


Something along the lines of...

CREATE PROC testsp


AS
SELECT
    col1,
    col2
FROM
    yourTable
ORDER BY




GO

--
- Anith

 
 
 

1. Specifying sort order as a stored procedure parameter

I would like to create a procedure with sort order as one of the
parameters. I have tried the following two ways, but I get an error
message saying:

Msg 1008, Level 15, State 1
The select list item identified by the order by number '1' contains a
variable as part of the expression that identifies a column position.
Variables are only allowed when ordering by an expression referencing a
column name.

CREATE PROCEDURE customer_view

AS
        SELECT  cust_lastname, cust_firstname, cust_id
        FROM customer

CREATE PROCEDURE customer_view

AS
        SELECT  cust_lastname, cust_firstname, cust_id
        FROM customer

Any ideas how to proceed?

Thanks
Salla Myllyla

2. Data Grid RowColChange Event

3. Sorting Fields by Asc or Desc order How?!?

4. OSX 10.1 Server?

5. ORDER BY DESC returning ASC not DESC !?

6. Sql Path

7. Order by desc slower than order by asc

8. Master scrolling, Detail not following

9. Specifying stored procedure parameter order with ADO.

10. asc/desc order in indexes?

11. ORDER BY (DESC, ASC) question

12. Conditional ORDER BY with conditional ASC or DESC