Re: using SELECT to call a STORED PROCEDURE
Ben,
A possible way around this limitation is to create an extra "interface"
procedure for each column you want your "real" stored procedure to
return. So, if you want 2 columns returned, you create 3 procedures,
something like in the following simple example:
The select statement:
select pr_value1(param),pr_value2(param) from dummy;
The stored procedures:
create procedure pr_value1(param) returning char(1); define param char(16);
define value1 char(1);
define value2 char(2);
execute procedure real_proc(param) returning value1,value2; return value1;
end procedure;
create procedure pr_value2(param) returning char(2); define param char(16);
define value1 char(1);
define value2 char(2);
execute procedure real_proc(param) returning value1,value2; return value2;
end procedure;
create procedure real_proc(param) returning char(1),char(2); define param char(16);
define gl_param char(16) default null;
define gl_value1 char(1);
define gl_value2 char(2);
if param <> gl_param or gl_param is null then
let gl_value1 = param[1,1]; -- Put your
let gl_value2 = param[2,3]; -- logic here.
end if
return gl_value1,gl_value2;
end procedure;
Whichever interface procedure (pr_value1 or pr_value2) is actually
executed first by the database, it will call the real procedure which
caches the values in global variables for the next interface procedure.
That way the actual logic is only executed once.
You could also leave your original stored procedure unchanged and let
the interface procedures take care of the caching. Anyway, some
variation of this theme might work for you.
Best wishes,
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
john@rl.is http://www.rl.is/~john/pow4gl.html
benhen@my-dejanews.com wrote:
>
> Hi there,
>
> I use Informix online 7.24
> I want to call a store procedure with 'SELECT'.
> (I don't want to use 'execute procedure' or 'call')
>
> I've try this statement :
>
> select proc_name(param) from dummy_table_with_one_row;
>
> When the procedure returns ONE value, it works fine, but when
> the procedure returns more than one value, I receive the message :
>
> '684: Procedure (informix.proc_name) returns too many values.'
>
> As I'm not a SQL guru, I ask if someone could help me to resolve this
> problem (any idea or workaround is welcome).
>
> Thanks already,
>
> Ben.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum