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.

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

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