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.
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.