---------- Original Message -----------
From: K Z <peixinhosda...@yahoo.com>
> Is there a way to make something like this:
> query="where ID=10 and AGE >20";
> select * from TABLE :query;
------- End of Original Message -------
create procedure filtered_select (/* parameters, preferably NOT just a text
version of your WHERE clause, as that would lead straight to SQL injection
issues */
param1 integer,
param2 varchar(100) default NULL,
...
param99 integer default NULL
/* if you place your most-used parameters first, and set the subsequent ones to
have a DEFAULT, you can call this stored procedure without filling every
parameter position */
) returns (
/* standard columns that would be coming back from select* */
a integer,
b integer,
c varchar(100),
...
z integer
) as
declare variable this_query varchar(10000);
declare variable this_filter varchar(10000);
begin
this_query = 'select * from blah';
-- no escaping used here for integer fields
if (param1 is not null) then
this_filter = coalesce(this_filter || ' and ', '') || ' x = ' || param1;
-- escaping for character fields
if (param2 is not null) then
this_filter = coalesce(this_filter || ' and ', '') || ' y = ''' || replace
(param2, '''', '''''') || '''';
/* ... */
this_query = this_query || coalesce(' where ' || this_filter, '');
for execute statement :this_query into :a, :b, :c, ..., :z do
suspend;
end
select * from filtered_select(3 /* param2 has a default of NULL if not passed
*/);
-- Note: no differentiation between "want to search for NULL" and "do not want
to filter by this parameter"
