Hi,

I have written a stored procedure in which I have to get the output sorted on different columns. I have written the following cursor

DECLARE cursor_name CURSOR WITH RETURN FOR
SELECT
*
FROM
<table_name<
ORDER BY IN_COLUMN_NM ASC ;

I am providing column name as an input parameter on which I want to sort my result set.

I am not getting any error. I am getting the same output for any value of IN_COLUMN_NM which is not sorted on any column.

Thanks in advance

Regards
Arun

You need dynamic SQL for that. I.e. build the statement as a string, then declare a cursor for it and open that cursor.

The reason for dynamic is simply that DB2 (or any other serious DBMS) cannot generate a single access plan with different sort requirements in the picture. For example, if you have a simple query that selects from a table, and you order by a column on which an index exists, then DB2 can fetch based on the index and doesn't have to apply a SORT operator itself. If no such index is available, you have a high chance that the SORT operator is necessary. Depending on the size of the table, the SORT operator may be expensive if, for example, it has to spill to disk. So different plans for different sort criteria are usually a good idea.

What about using a case statement in the order by? Something along the lines of:

ORDER BY (CASE WHEN :IN_COLUMN_NM = ? THEN COL1
WHEN :IN_COLUMN_NM = ? THEN COL2
ELSE COL3
END)