Dynamic ORDER BY clause

[letmatros]

Hi, I have a fairly simple select statement, but I want to do pagination and the sort order should be controlled by the user. The last part is causing me problems...

I use DB2 8.2.3 on Windows. My idea is to create a prepared statement (or a named query) instead of using dynamic SQL.

SELECT * FROM (SELECT ROWNUMBER() OVER(
ORDER BY CASE CAST(? AS SMALLINT)
WHEN 1 THEN a.firstname
WHEN 2 THEN a.lastname
WHEN 3 THEN a.workphone
WHEN 4 THEN a.title
ELSE a.lastname
END)
AS rownumber_,
a.contactid, a.firstname, a.lastname, a.workphone, a.title
FROM Contact a
WHERE a.contactId = ? AND a.deleted = 0
ORDER BY CASE CAST(? AS SMALLINT)
WHEN 1 THEN a.firstname
WHEN 2 THEN a.lastname
WHEN 3 THEN a.workphone
WHEN 4 THEN a.title
ELSE a.lastname
END
) as temp_ where rownumber_ between ? and ?

Is this really the right approach and how do I get to control the sort order (ascending/descending)?

What is the performance impact of using dynamic sql? I'm thinking it would be easier to maintain.

Best Regards,
Claus

[Snib]

Dynamic SQL may be bad if you have a large amount of data on the table and start doing complicated predicate searches or repeated accesses in loops - you will have no idea what access path Db2 will pick until the dynamic SQL is run. You can attempt to predict it using EXPLAINs but if the RUNSTATS of the tabels you use fo the EXPLAIN change, which they will as the data on the tables grow, then the access paths for the dynamic SQL could change.

But, in the example code you only seem to have 4 order by types. It may be easier just to setup 4 static peices of SQL which return exactly the same columns but with different ORDER BY statements. The user can then select the order they want the data displayed in one the screen and your program just picks which peice of SQL to run.

Regards

Snib