stored procedure help

dushan1987 · #1 (**permalink**) 03-21-11, 05:38

i want to write stored procedure to return bp_praccno char(5) and bp_prname char(20) values from the table called bt_provider. how can i do that?? pls help me. thank u

dushan1987 · #2 (**permalink**) 03-21-11, 05:46

name of the database is "hdfc" and returning two things are a account number and a provider name. thanks

InformixWilli · #3 (**permalink**) 03-21-11, 06:49

Code:

create procedure test () returning char(5), char(20);
  define bp_praccno char(5);
  define bp_prname char(20);
  foreach c1 for
    select customer_num, lname into bp_praccno,bp_prname from bt_provider
    return bp_praccno, bp_prname  with resume;
  end foreach;
end procedure;

select bp_praccno, bp_prname 
from table(test())(bp_praccno,bp_prname)
where bp_praccno > 110;

Didn't test but should work.

dushan1987 · #4 (**permalink**) 03-21-11, 23:22

thank u sir, but it gives me a error
674: Routine (get_provider) can not be resolved.
111: ISAM error: no record found.
what u mean by customer_num and lname in ur procedure ??
what s the meaning of where bp_praccno > 110;??

InformixWilli · #5 (**permalink**) 03-22-11, 03:13

Sorry

Code:

create procedure test () returning char(5), char(20);
  define bp_praccno char(5);
  define bp_prname char(20);
  foreach c1 for
    select bp_praccno,bp_prname into bp_praccno,bp_prname from bt_provider
    return bp_praccno, bp_prname  with resume;
  end foreach;
end procedure;

select bp_praccno, bp_prname 
from table(test())(bp_praccno,bp_prname)
where bp_praccno > 110;

dushan1987 · #6 (**permalink**) 03-22-11, 03:21

thank u very much

Internet Services

Web Development

Digital Marketing

Consumer Tech