--
-- ### Advanced procedure checks
-- 
-- 
--

drop if exists table t1;
drop if exists table t2;
create table t1 ( a int, b string(30));
create table t2 ( a int, acopy int, bcopy string(30), c string(10));

drop if exists counter aid;
create counter aid;
set counter aid to 0;

drop if exists procedure newEntry;

@
create procedure newEntry ( b in string(30) ) return int 
begin 
   var a int;
   :a = nextcount(aid);
   insert into t1 values ( :a, :b );
   return :a;
end;
@


drop if exists procedure copyTab;
@
create procedure copyTab () return int 
begin 
   var a int;
   :a = nextcount(aid);
   insert into t2 select :a, a, b, 'COPY' from t1;
   return :a;
end;
@

drop if exists procedure cleanEntry;
@
create procedure cleanEntry ()
begin 
   delete from t1;
   delete from t2;
   setcount(aid, 0);
end;
@

call newEntry('Erwin');
call newEntry('Matha');
call newEntry('Hugo');
call copyTab();
call copyTab();

select * from t1;
select * from t2;


call cleanEntry();

select * from t1;

call newEntry('Erwin');
select * from t1;

show procedure newEntry;
show procedure copyTab;
show procedure cleanEntry;
