delimiter /go
drop procedure project_create
/go
create procedure project_create (
p_programme integer
, p_PROJTYPE varchar(10)
, p_code varchar(10)
, p_name varchar(50)
, p_site integer
, p_modified_by varchar(10)
, out p_id integer
, out p_msg varchar(255)
)
begin
/* set abort code */
declare progress varchar(50) default 'set handler';
declare exit handler for sqlwarning, sqlexception begin
rollback;
set p_msg = concat('project_create: aborted following sql error around ', progress);
end;
/* check params */
set progress = 'check parameters';
if not exists (select id from programme where id = p_programme) then
set p_id = 0,
p_msg = concat('project_create: unknown programme id ', ifnull(convert(p_programme, char), '(null)'));
elseif not exists (select code from refdata where domain = 'PROJTYPE' and code = p_PROJTYPE) then
set p_id = 0,
p_msg = concat('project_create: unknown project type ', ifnull(p_PROJTYPE, '(null)'));
elseif ifnull(p_code, '') = '' then
set p_id = 0,
p_msg = 'project_create: null or blank project code';
elseif exists (select code from programme where code = p_code) then
set p_id = 0,
p_msg = concat('project_create: duplicate project code ', p_code);
elseif ifnull(p_name, '') = '' then
set p_id = 0,
p_msg = 'project_create: null or blank project name';
elseif not exists (select id from site where id = p_site) then
set p_id = 0,
p_msg = concat('project_create: unknown site id ', ifnull(convert(p_site, char), '(null)'));
elseif not exists (select code from login where code = p_modified_by) then
set p_id = 0,
p_msg = concat('project_create: unknown login ', ifnull(p_modified_by, '(null)'));
else
/* params ok - create project */
start transaction;
set progress = 'insert project';
insert into project (programme, PROJTYPE, code, name, site, modified_by)
values (p_programme, p_PROJTYPE, p_code, p_name, p_site, p_modified_by);
set p_id = last_insert_id();
set progress = 'insert project_stages';
insert into project_stage (project, stage)
select p_id, s.code
from refdata_set pts
join stage s on s.code = pts.child_code
where pts.parent_domain = 'PROJTYPE' and pts.parent_code = p_PROJTYPE
order by s.seq;
set p_msg = 'Project created';
set progress = 'insert history';
call history_create('PRJ', 'INS', p_id, concat('created as type ', p_PROJTYPE), p_modified_by);
commit;
end if;
end
/go