--- In firebird-support@yahoogroups.com, Fabrício Fadel Kammer
<ffkammer@c...> wrote:

>
> Thanks Adam,
>
> But do you understand what I want?
>
> I need to recover all the customers of the "CUSTOMERS" table if the
> pCode parameter is NULL and I need to recorver just one customer that
> have the CUSTOMER.CODIGO (primary key of the customer table) equal to
> the pCode parameter if it was informed.
>
> Are there another form to do this?
>

Well normally you dont include the where clause in the query from the
client if you want all records. But you can do it using a stored
procedure.

set term ^ ;

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

end
^

Then you can use

select * from getcustomers(null)

or

select * from getcustomers(1)

The stored procedure internally adjusts the query and runs it.

Do you understand how parameters differ from some block of text that
is replaced?

Adam

> Thanks again
>
> Fabrício F. Kammer
>
> -----Mensagem original-----
> De: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] Em nome de Adam
> Enviada em: quinta-feira, 5 de janeiro de 2006 20:31
> Para: firebird-support@yahoogroups.com
> Assunto: Re: RES: [firebird-support] Null and parameters
>
>
> You can emulate what you want using the execute statement syntax in
> any PSQL (trigger or stored procedure) block, however it is mostly
> unnecessary.
>
> Parameters are pseudo-values, not variables. You can generate the
> ideal plan for a query using its parameters, but there is no way to
> determine the query plan for
>
> SELECT C.NOME FROM CUSTOMERS AS C WHERE :pCode IS NULL OR C.CODIGO =
> :pCode;
>
> In this case, you are trying to use pCode as a field name, sort of
> like WHERE ID IS NULL. If ID is indexed, then the index could be used
> to assist the query.
>
> A Null parameter is not the problem here.
>
> SELECT C.NOME FROM CUSTOMERS AS C WHERE C.CODIGO = :pCode;
>
> Will work fine if :pCode is sent through as null. There may be a case
> for an enhancement request to allow a form of find/replace variables,
> but it is not available now.
>
> Adam
>
>
>
>
>
>
>
> --- In firebird-support@yahoogroups.com, Fabrício Fadel Kammer
> <ffkammer@c...> wrote:
> >
> > Thanks Jason,
> >
> > It's I can do this in oracle and I couldn't doing on firebird...
> >
> > Regards
> >
> > Fabrício
> >
> > -----Mensagem original-----
> > De: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] Em nome de Jason Dodson
> > Enviada em: quinta-feira, 5 de janeiro de 2006 13:44
> > Para: firebird-support@yahoogroups.com
> > Assunto: Re: [firebird-support] Null and parameters
> >
> >
> > You cant use a parameter in place of a field name. Its not quite
> > find-and-replace.
> >
> > Jason
> >
> > Fabrício Fadel Kammer wrote:
> > > Hi all,
> > >
> > > I'm with another doubt here!
> > >
> > > Can't I use the NULL for parameters comparison like bellow?
> > >
> > > SELECT C.NOME FROM CUSTOMERS AS C WHERE :pCode IS NULL OR C.CODIGO =
> > > :pCode;
> > >
> > > In this situation I want to select all customers if the pCode
> > parameter
> > > is NULL or just one customer if the :pCode parameter was informed.
> > >
> > > Thanks again,
> > >
> > > Fabrício F. Kammer
> > > Conchal/SP Brazil
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >
> > > Visit http://firebird.sourceforge.net and click the Resources item
> > > on the main (top) menu. Try Knowledgebase and FAQ links !
> > >
> > > Also search the knowledgebases at http://www.ibphoenix.com
> > >
> > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> > --
> > The information transmitted herewith is sensitive information intended
> > only for use to the individual or entity to which it is addressed. If
> > the reader of this message is not the intended recipient, you are
> hereby
> > notified that any review, retransmission, dissemination, distribution,
> > copying or other use of, or taking of any action in reliance upon,
> this
> > information is strictly prohibited. If you have received this
> > communication in error, please contact the sender and delete the
> > material from your computer.
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://firebird.sourceforge.net and click the Resources item
> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >
> >
> >
> > _____
> >
> > YAHOO! GROUPS LINKS
> >
> >
> >
> > * Visit your group "firebird-support
> > <http://groups.yahoo.com/group/firebird-support> " on the web.
> >
> >
> > * To unsubscribe from this group, send an email to:
> > firebird-support-unsubscribe@yahoogroups.com
> >
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe
> > >
> >
> >
> > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> > Service <http://docs.yahoo.com/info/terms/> .
> >
> >
> > _____
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
> _____
>
> YAHOO! GROUPS LINKS
>
>
>
> * Visit your group "firebird-support
> <http://groups.yahoo.com/group/firebird-support> " on the web.
>
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe
> >
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/> .
>
>
> _____
>
>
>
>
> [Non-text portions of this message have been removed]
>