Subject Re: [firebird-support] Re: How to pass a where clause to a stored procedure
Author André Knappstein
> I got a test Execute Statement to work without any arguments and then found this:-

> 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))
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);
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;

execute Statement :V_Command
:V_001Z, :V_002N, :V_003N, :V_004N
OP_001Z = :V_001Z;
OP_002N = :V_002N;
OP_003N = :V_003N;
OP_004N = :V_004N;