#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    Produre with SELECT


    Hello guys,

    I need to make a procedure with a select that use parameter..

    With the parameter ID if 0, return all the records, or if the parameter ID is other number, like 1, the procedure will return the record with parameter 1.(where)

    Thank you

    IF id = 0 then

    select ID, Nome, Profissao, Idade
    from pessoal
    into:
    PAR_CODIGO,
    :PAR_NOME,
    :PAR_PROFISSAO,
    :PAR_IDADE

    else

    select ID, Nome, Profissao, Idade
    from pessoal where ID = :ID
    into:
    PAR_CODIGO,
    :PAR_NOME,
    :PAR_PROFISSAO,
    :PAR_IDADE
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Originally Posted by pedrohgb7
    Hello guys,

    I need to make a procedure with a select that use parameter..

    With the parameter ID if 0, return all the records, or if the parameter ID is other number, like 1, the procedure will return the record with parameter 1.(where)

    Thank you

    IF id = 0 then

    select ID, Nome, Profissao, Idade
    from pessoal
    into:
    PAR_CODIGO,
    :PAR_NOME,
    :PAR_PROFISSAO,
    :PAR_IDADE

    else

    select ID, Nome, Profissao, Idade
    from pessoal where ID = :ID
    into:
    PAR_CODIGO,
    :PAR_NOME,
    :PAR_PROFISSAO,
    :PAR_IDADE
    You don't need stored procedure for this query. The query is simple.
    Just use :
    Code:
    select      
        ID,      
        Nome,      
        Profissao,      
        Idade  
    from      
        pessoal  
    where      
        (ID = :ID) or (:ID = 0)
    But if you still want sp:
    Code:
    create or alter procedure proc1(id  enter_type_here)
    returns(
    PAR_CODIGO enter_type_here,
          PAR_NOME enter_type_here,
          PAR_PROFISSAO enter_type_here,
          PAR_IDADE enter_type_here)
    do begin
    for select 
        ID, 
        Nome, 
        Profissao, 
        Idade 
    from 
        pessoal 
    where 
        (ID = :ID) or (:ID = 0)
    into 
       :PAR_CODIGO, :PAR_NOME, :PAR_PROFISSAO, :PAR_IDADE
    do
        suspend;
    end
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Hello, my friend,

    I used this:

    select
    ID,
    Nome,
    Profissao,
    Idade
    from
    pessoal
    where
    (ID = :ID) or (:ID = 0)

    but when i put 0 on the parameter, don't return no record.
    thank you
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    ok, my friend.

    I get it...

    How would I do that?
    2 parameters, 1 integer and other string. I the parameter comes empty, returns all the records.

    select
    ID,
    Nome,
    Profissao,
    Idade
    from
    pessoal
    where
    (Nome like :Nome || '%') or (:Nome like '') and
    (ID = :ID) or (:ID = 0)

    can you send me any tutorial of firebird...or where can I find?

    thank you
    Last edited by pedrohgb7; August 19th, 2013 at 11:15 AM. Reason: adjust
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    You need SQL tutorial. I don't know where you can't find one, because I never need it(when I started to work with SQL I used books to learn it )
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    19
    Rep Power
    0
    Useful one might be

    Firebird 2.5 Language Reference Update

    or

    Firebird: Documentation

    to get you started

IMN logo majestic logo threadwatch logo seochat tools logo