CODEWITHSUNDEEP
c#oracle-databasestored-proceduresplsqlsql-order-by
WarChild
WarChild

Reputation: 61

Stored Procedure Dynamic Order By

Hi fellow stackoverflow members,

I have a pressing question here.

I am currently developing a mini web application with a GridView. The GridView must be able to perform multi column sorting dynamically. Whenever user click on the different columns, my C# app will parse the columns and sort order asc/desc to the stored procedure to perform retrieval of records. As such, my SP must be able to handle the inputs dynamically.

I don't wish to use dynamic SQL and wish to stick to PL/SQL queries.

Here's a sample of my code,

SELECT ID,NAME FROM MY_TABLE WHERE ID='123' ORDER BY ID ASC, NAME DESC;

This part, ORDER BY ID ASC, NAME DESC must be dynamic. I wish to formulate this part based on the parameters passed in from my application.

I am currently messing around with DECODE function and CASE function and have mixed results as i am only able to handle 1 parameter input. I need to be able to handle multiple column sorting if users click on multiple columns on my GridView.

This part should be dynamic and able to handle multiple column sorting.

ORDER BY ID ASC, NAME DESC, CLASS ASC ... and so on and so forth .

Fellow members please point me in the right direction. Thanks!

1) Don't wish to use dynamic sql and achieve the above in PL/SQL.

2) Parameters input customizable, ok with anything on front end. Need help with the SP.

Regards, TP

Upvotes: 3

Views: 2999

Answers (4)

Aarif Qureshi
Aarif Qureshi

Reputation: 474

Pass the 

DECLARE @OrderBy VARCHAR(10)

DECLARE @ColumnName varchar(50)

this two arguments as an input parameter for Stored procedure

Try the following CASE condition as:

ORDER BY CASE WHEN @OrderBy = 'ASC' THEN @ColumnName END ASC,
CASE WHEN @OrderBy = 'DESC' THEN @ColumnName END DESC

Upvotes: 0

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

This should get you going in the right direction:

CREATE OR REPLACE PROCEDURE GET_DETAILS (ORDER_TYPE1   IN VARCHAR2(5)
                         ORDER_TYPE2   IN VARCHAR2(5)
                                         ORDER_FIELD1 IN VARCHAR2(5) 
                                         ORDER_FIELD2  IN VARCHAR2(5)
                     V_ID         IN VARCHAR2(5)
                                         OUTPUT_DATA  OUT REF_CURSOR)
    IS
BEGIN 

OPEN OUTPUT_DATA FOR 
SELECT ID,NAME FROM MY_TABLE WHERE ID=V_ID ORDER BY ORDER_FIELD1 ORDER_TYPE1, ORDER_FIELD2 ORDER_TYPE2;

END GET_DETAILS;

ORDER_TYPE1,ORDER_TYPE2,ORDER_FIELD1 etc are inputs The output of this procedure will be the set of records as you want

Upvotes: 0

Aarif Qureshi
Aarif Qureshi

Reputation: 474

You can pass Parameter for that (@SortIndex) 

ORDER BY 
CASE WHEN @SortIndex = 1 THEN ID END ASC,
CASE WHEN @SortIndex = 2 THEN NAME END DESC
CASE WHEN @SortIndex = 3 THEN Title END ASC

Upvotes: 4

wolφi
wolφi

Reputation: 8361

It's not elegant, but it should work. Supply a code number and sort accordingly:

ORDER BY CASE myparam 
         WHEN  1 THEN col1 ASC 
         WHEN -1 THEN col1 DESC
         WHEN  2 THEN col2 ASC 
         WHEN -2 THEN col2 ASC
         END;

Upvotes: 0

Related Questions