zOs/SQL/STPRSEL

$<=[
set current sqlid = 's100447' ;
drop   procedure A540769.selP1;
drop   procedure A540769.selP2;
drop   procedure gdb9998.selP1;
drop   procedure gdb9998.selP2;
terminator    } ;
create procedure gdb9998.selP1(in name varchar(20), out cnt int
                  , out vers varchar(20), out aPrm varchar(30))
        version v1 deterministic reads sql data
    begin
    declare sqlCode int default 0;
    declare exit handler for not found
        begin
        set aPrm = 'not found ' || char(sqlCode);
        end;
    select count(*), min(version) into cnt, vers
        from sysibm.sysRoutines r
        where r.name = selP1.name;
    select char(parm_count)  into aPrm
        from sysibm.sysRoutines r
        where r.name = selP1.name
        and   active = 'Y';
    end
}
terminator ; }
call gdb9998.selP1(SELP1,  :c, :v, :p);
call gdb9998.selP1(VERSP1, :c, :v, :p);
call gdb9998.selP1(xxxYY,  :c, :v, :p);
commit;
$] call sqlStmtsOpt
$#out                                              20110608 14:37:51
sqlCode 0: set current sqlid = 's100447'
sqlCode 0: drop   procedure A540769.selP1
sqlCode -204: drop   procedure A540769.selP2
sqlCode -204: drop   procedure gdb9998.selP1
sqlCode -204: drop   procedure gdb9998.selP2
sqlCode 0: create procedure gdb9998.selP1(in name varchar(20), out cnt ...
--- called GDB9998.SELP1, sqlCode 0
  parm 1 i NAME = SELP1
  parm 2 o CNT $c = 1
  parm 3 o VERS $v = V1
  parm 4 o APRM $p = 4
sqlCode 0: call gdb9998.selP1(SELP1,  :c, :v, :p)
--- called GDB9998.SELP1, sqlCode 0
  parm 1 i NAME = VERSP1
  parm 2 o CNT $c = 2
  parm 3 o VERS $v = V1
  parm 4 o APRM $p = 1
sqlCode 0: call gdb9998.selP1(VERSP1, :c, :v, :p)
--- called GDB9998.SELP1, sqlCode 0
  parm 1 i NAME = xxxYY
  parm 2 o CNT $c = 0
  parm 3 o VERS $v =
  parm 4 o APRM $p = not found 100
sqlCode 0: call gdb9998.selP1(xxxYY,  :c, :v, :p)
sqlCode 0: commit
$#out                                              20110603 18:17:24
sqlCode 0: set current sqlid = 's100447'
sqlCode 0: drop   procedure gdb9998.selP1
sqlCode -204: drop   procedure gdb9998.selP2
sqlCode 0: create procedure gdb9998.selP1(in name varchar(20), out cnt ...
--- called gdb9998.SELP1, sqlCode 0
  parm 1 i NAME = SELP1
  parm 2 o CNT $c = 1
  parm 3 o VERS $v = V1
  parm 4 o APRM $p = 4
sqlCode 0: call gdb9998.selP1(SELP1,  :c, :v, :p)
--- called gdb9998.SELP1, sqlCode 0
  parm 1 i NAME = VERSP1
  parm 2 o CNT $c = 2
  parm 3 o VERS $v = V1
  parm 4 o APRM $p = 1
sqlCode 0: call gdb9998.selP1(VERSP1, :c, :v, :p)
--- called gdb9998.SELP1, sqlCode 0
  parm 1 i NAME = xxxYY
  parm 2 o CNT $c = 0
  parm 3 o VERS $v =
  parm 4 o APRM $p = not found 100
sqlCode 0: call gdb9998.selP1(xxxYY,  :c, :v, :p)
sqlCode 0: commit
$#out                                              20110530 11:13:45