Help for Dynamically changing sort by field and order by clause, in db2 procedure

[Biju moozhikkara]

Hello,

i have requirement where i have to do order by on the field that i give as input to the store procedure.Also i have to change the sorting direction, ASC or DESC

folowing is the stored proc i tried.Although its error free and getting deployed .the result returned on execution is,not sorted as per the input i gave.

CREATE PROCEDURE SP_TESTPAGE (
IN current_sort_field VARCHAR(64),
IN current_sort_order VARCHAR(64),
IN sso_id VARCHAR(64)
)
DYNAMIC RESULT SETS 1
P1: BEGIN

-- Declare cursors
DECLARE cursor CURSOR WITH RETURN FOR
SELECT SESSION_ID, FROM_DATE, DESCRIPTION FROM TACTP00.PAGINATION
ORDER BY current_sort_field ;


OPEN cursor;
--END CASE;
END P1

The another aproach i tried was to put seperate select query with order by on each cloumn with a declared cursor with it.And open only one cursor based on the check whether current_sort_field(input to stored proc) is column1 or column2.This works fine, but this will bring up lots of if conditons in the stored proc as the number of columns in the table increases. It would be great if i can make the first approach work.

Can any body help

Regards

Biju

[Arulanand Dayalan]

You Can use Dynamic Sql. Build the Query using the input parameters. Use EXECUTE IMMEDIATE to execute it.

Hope this Helps.

Thanks and Regards,
Arul.

[Biju moozhikkara]

Can any body provide me with Any sample working code or tutorial for dynamic sql in db2 procedures.


Regards
Biju M

[Tracy Nelson]

Dynamic SQL is the opposite of stored procedures. What you do is to use a StringBuffer to create your SELECT statement, appending your SORT BY and ORDER BY clauses. Create a PreparedStatment, passing your StringBuffer as the statement to execute. Then just call executeQuery().

If your shop requires that all SQL be performed via stored procedures, then you should talk to your DBA and see if they have any suggestions. Otherwise, you might want to write a small utility that goes out to the database, grabs the metadata for the table you're querying, and generates the stored procedure with the IF statements for every column. At least that way, you don't have to go in and update the SP by hand every time the table schema changes.