> I got a test Execute Statement to work without any arguments and then found
> this:-
> http://www.firebirdsql.org/refdocs/langrefupd15-psql-execstat.html
> They say "The argument string cannot contain any parameters." which
> is basically what i wanted to do. ie pass a string containing the arguments.
I don't understand too good where your problem is.
The following is a simplified example which works fine for me with 1
variable fieldname and a variable value to form the where constraint.
It is quite easy to extend this model to use more than one
fieldname-value pairs, and also it is possible to make the condition
[<, >, <=, >=...] variable instead of static.
I am excited that this thing works so nice, and in fact it did already
in FB 1.5, but I only use this model rarely, where I indeed
want that a user can "click-create" a fieldlist or a
where-constraint, and the number of potentially resulting
different queries is very very high.
And I only do so, because I can make sure that *only* my application
is executing that stored proc.
create procedure SP_EXEC_WITH_ARGUMENTS (
IP_FIELDNAME varchar(10),
IP_VALUE decimal(15,2))
returns (
OP_001Z varchar(10),
OP_002N decimal(15,2),
OP_003N decimal(15,2),
OP_004N decimal(15,2))
as
declare variable V_WHERECLAUSE varchar(512);
declare variable V_COMMAND varchar(1024);
declare variable V_001Z varchar(10);
declare variable V_002N decimal(15,2);
declare variable V_003N decimal(15,2);
declare variable V_004N decimal(15,2);
begin
V_WhereClause = 'Where '||F_LRTrim(:IP_FieldName)||' >=
'||F_LRTrim(:IP_Value);
V_Command = 'Select Field_1, Field_2, Field_3, Field_4 from Table_1
'||:V_WhereClause;
For
execute Statement :V_Command
into
:V_001Z, :V_002N, :V_003N, :V_004N
do
begin
OP_001Z = :V_001Z;
OP_002N = :V_002N;
OP_003N = :V_003N;
OP_004N = :V_004N;
suspend;
end
end
