Dynamic SQL in Stored Procedure

From: Mark Aurit <mark_aurit_at_mail.northgrum.com>
Date: 1998/01/29
Message-ID: <01bd2d11$9d2b5a80$3d846380@NGW20072982.f18.masd.northgrum.com>#1/1

Im using the Oracle Web Server, and I want to give the customer the ability choose a sort; in other words, affect the ORDER BY. Their request will be passed to a stored procedure, and Im wondering how (and if) I can change the SQL dynamically. I could do something like IF var1 = 'A' THEN

	.. do some cursor stuff and issue a SELECT..
	ORDER BY colA
ELSE
           ORDER BY colB

END IF;
But I hate the idea of all that code Ill have to duplicate. Can I say something like:
IF var1 = 'A' then

        var2 := 'emp_id';
else

        var2 := 'city';
end if;
Followed by:
.. cursor stuff and a SELECT ...
ORDER BY var2

Or some such construct?

Any ideas greatly appreciated.
Mark_Aurit @ mail.northgrum.com Received on Thu Jan 29 1998 - 00:00:00 CST