--- In firebird-support@yahoogroups.com, Christian Danner
<christian@d...> wrote:

>
> Hi Adam,
>
> on Fri, 06 Jan 2006 11:51:35 -0000, you wrote:
>
> >create procedure getcustomers
> >(
> >pCode integer
> >)
> >returns
> >(
> >Codigo Integer,
> >NOME varchar(100)
> >)
> >as
> >declare variable stmt varchar(200)
> >begin
> >stmt = 'SELECT c.Codigo, c.Nome FROM CUSTOMERS C';
> >
> >if (:pCode is not null) then
> >begin
> > stmt = stmt || ' WHERE C.CODIGO = ' || :pcode;
> >end
> >
> >for execute statement stmt
> >into :Codigo, :Nome do
> >begin
> > suspend;
> >end
>
> Shouldn't either 'execute statement' or a sp be used, but not
> necessarily both.

You will have a fun time trying to use execute statement without a
stored procedure (or trigger) considering it is part of the PSQL
language rather than SQL.

>
> Within a sp the originally discussed syntax does work, no problematic
> client side replacement of the variable.

Have you tested this? If so I wouldn't regard it as future proof
behaviour because parameters are substitutable values not a random
string to be inserted at runtime. You may find a future version breaks
this behaviour.

Adam

>
> So why not:
>
> create procedure getcustomers (
> pcode integer)
> returns (
> codigo integer,
> nome varchar(100))
> as
> begin
> for select c.codigo, c.nome
> from customers c
> where (:pcode is null)
> or (c.codigo = :pcode)
> into :codigo, :nome
> do
> suspend;
> end
>
> Regards
>
> Christian
>