--
-- ### Procedure checks
--


create table srctab ( a int, b string(30));
create table desttab ( a int, b string(30));
create index srctab_i1 on srctab(a);

insert into srctab values ( 1, 'fred');
insert into srctab values ( 2, 'mark');
insert into srctab values ( 3, 'berta');
insert into srctab values ( 4, 'john');
insert into srctab values ( 5, 'anna');

@
create procedure copytab ( copyCond in int ) return int 
begin 

   var copyCount int;
   :copyCount = 0;
 
   var ca int;
   var cb string(30);

   cursor copyCursor as select a, b from srctab where a = :copyCond;

   while fetch copyCursor into ( :ca, :cb ) = true
   begin
      insert into desttab values ( :ca, :cb );
      :copyCount = :copyCount + 1 ;
   end;

   return :copyCount;

end;
@

set a = 5;
:r = call copytab(:a);
print 'Return value = ' | :r;

select * from desttab;

delete from desttab;

insert into desttab (  a, b ) select a, b from srctab;

select * from desttab;

drop table srctab;
drop table desttab;
drop procedure copytab;


@
create procedure myproc1 ( a in int ) return int 
begin 

   return :a + 1;

end;
@

@
create procedure myproc2 ( b in int ) return int 
begin 

   var ma int;

   :ma = myproc1( 3 * :b );

   return :ma + 1;

end;
@

set x = 5;
:r = call myproc2(:x);
print :r;


drop procedure myproc1;
drop procedure myproc2;
