DELIMITER $$

DROP PROCEDURE IF EXISTS `UsrAdd` $$
CREATE DEFINER=`hospital`@`%` PROCEDURE `UsrAdd`(

login_ varchar(30), pwd_ char(32), fio_ varchar(60), loginon_ tinyint(1), ui_ int(10)

)
bg:BEGIN

declare c int(10);
declare msg varchar(200);
declare s varchar(200);

rollback;
start transaction;

if login_ is null or trim(login_)='' then

	set msg='Пользователь не добавлен. Логин пуст.';
  signal sqlstate '03000' set message_text=msg;
	rollback;
	leave bg;
end if;

insert into usr (login, pwd, fio, loginon, ts, di, ui)
values (trim(login_), pwd_, trim(fio_), coalesce(loginon_,0), 0, sysdate(), ui_);

select last_insert_id() as code;
set c=last_insert_id();

#добавление ролей
insert into adm_usrroles (rf_usrid, rf_adm_roleid, ts, ui, di)
select c, rf_adm_roleid, 0, ui_, sysdate()
from tmpUsrroles
where `add`=1 and del<>1;

commit;

END $$

DELIMITER ;