[firebird-support] Re: How to pass a where clause to a stored procedure

[andrew.pailtho...@btinternet.com]

Thanks for the replies.

I had a look at the Execute Statement approach and what I was looking for was 
to build the Where Clause/Arguments at the front end and pass as a parameter to 
a stored procedure.

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.


so rather than spending more time on this I've got a stored procedure to check 
the results myself. Heres a snippet:-

begin
    /* Was the Payment Terms Strict Days selected ? */
    v_pt_stricts_days_found = 0;
    if (:ip_pt_use_strict_days = 1) then
    begin
      if (op_payment_terms_strict_days between :ip_pt_strict_days_from and 
:ip_pt_strict_days_to) then
      begin
        v_pt_stricts_days_found = 1;
      end
    end

    /* Was the Payment Terms EOM Days selected ? */
    v_pt_eom_days_found = 0;
    if (:ip_pt_use_eom_days = 1) then
    begin
      if (op_payment_terms_eom_days between :ip_pt_eom_days_from and 
:ip_pt_eom_days_to) then
      begin
        v_pt_eom_notes_found = 1;
      end
    end

...



The arguments are optional therefore I cant build them into the where clause 
especially a comparison on a string field where the user can do a straight/like 
or containing comparison.

thanks