drop table if exists test;
create table test(
    id int not null auto_increment primary key,
    value int not null
--    value2 enum('one', 'two', 'three')
) engine=InnoDB;


-- -------------
-- VERSION funcs 1
-- -------------


delimiter $$$

drop function if exists __raiseError$$$
drop procedure if exists __raiseError$$$

create procedure __raiseError(msg varchar(128))
begin
    signal sqlstate '45000'
    set message_text = msg;
end$$$


drop function if exists raiseError$$$
drop procedure if exists raiseError$$$

create function raiseError(msg varchar(128)) returns int unsigned
not deterministic modifies sql data
begin
    call __raiseError(substring(msg, 1, 128));
    return 1;
end$$$

drop function if exists raiseError1$$$
drop procedure if exists raiseError1$$$

create function raiseError1(error_code varchar(30), subject varchar(30), msg varchar(65)) returns int unsigned
not deterministic modifies sql data
return raiseError(concat_ws('|', coalesce(error_code, '<empty>'), coalesce(subject, ''), coalesce(msg, '')))$$$

drop function if exists raiseError2$$$
drop procedure if exists raiseError2$$$

create function raiseError2(
    error_code varchar(30), 
    subject1 varchar(30), 
    subject2 varchar(30), 
    msg varchar(35)
) returns int unsigned
not deterministic modifies sql data
return raiseError(concat_ws('|', 
                    coalesce(error_code, '<empty>'), 
                    coalesce(subject1, ''), 
                    coalesce(subject2, ''), 
                    coalesce(msg, '')))$$$

drop function if exists testOddRaiseError$$$
                    
create function testOddRaiseError(n int) returns int
not deterministic modifies sql data
begin
    do (n % 2 = 1) and raiseError1('testcode', n, ' test message');
    insert test(value) values(n);
    return n;
end$$$

delimiter ;

-- -------------
-- VERSION funcs 2
-- -------------


delimiter $$$

drop function if exists __raiseError$$$
drop procedure if exists __raiseError$$$

create procedure __raiseError(msg varchar(128))
begin
    set @__errorMessage = msg;
    call `procedure that fails`();
end$$$


drop function if exists raiseError$$$
drop procedure if exists raiseError$$$

create function raiseError(msg varchar(128)) returns int unsigned
not deterministic modifies sql data
begin
    call __raiseError(substring(msg, 1, 128));
    return 1;
end$$$

drop function if exists raiseError1$$$
drop procedure if exists raiseError1$$$

create function raiseError1(error_code varchar(30), subject varchar(30), msg varchar(65)) returns int unsigned
not deterministic modifies sql data
return raiseError(concat_ws('|', coalesce(error_code, '<empty>'), coalesce(subject, ''), coalesce(msg, '')))$$$

drop function if exists raiseError2$$$
drop procedure if exists raiseError2$$$

create function raiseError2(
    error_code varchar(30), 
    subject1 varchar(30), 
    subject2 varchar(30), 
    msg varchar(35)
) returns int unsigned
not deterministic modifies sql data
return raiseError(concat_ws('|', 
                    coalesce(error_code, '<empty>'), 
                    coalesce(subject1, ''), 
                    coalesce(subject2, ''), 
                    coalesce(msg, '')))$$$

drop function if exists testOddRaiseError$$$
                    
create function testOddRaiseError(n int) returns int
not deterministic modifies sql data
begin
    if n % 2 = 1 then 
        do raiseError1('testcode', n, ' test message');
        insert test(value) values(n * 2);
    end if;
    insert test(value) values(n);
    return n;
end$$$

delimiter ;


-- -------------
-- VERSION procs 1
-- -------------


delimiter $$$

drop function if exists __raiseError$$$
drop procedure if exists __raiseError$$$

create procedure __raiseError(msg varchar(128))
begin
    signal sqlstate '45000'
    set message_text = msg;
end$$$


drop function if exists raiseError$$$
drop procedure if exists raiseError$$$

create procedure raiseError(msg varchar(128))
begin
    call __raiseError(substring(msg, 1, 128));
end$$$

drop function if exists raiseError1$$$
drop procedure if exists raiseError1$$$

create procedure raiseError1(error_code varchar(30), subject varchar(30), msg varchar(65))
begin
    call raiseError(concat_ws('|', coalesce(error_code, '<empty>'), coalesce(subject, ''), coalesce(msg, '')));
end$$$

drop function if exists raiseError2$$$
drop procedure if exists raiseError2$$$

create procedure raiseError2(
    error_code varchar(30), 
    subject1 varchar(30), 
    subject2 varchar(30), 
    msg varchar(35)
)
begin 
    call raiseError(concat_ws('|', 
                    coalesce(error_code, '<empty>'), 
                    coalesce(subject1, ''), 
                    coalesce(subject2, ''), 
                    coalesce(msg, '')));
end$$$

drop function if exists testOddRaiseError$$$
                    
create function testOddRaiseError(n int) returns int
not deterministic modifies sql data
begin
    if (n % 2 = 1) then 
        call raiseError1('testcode', n, ' test message');
        insert test(value) values(n * 2);
    end if;
    insert test(value) values(n);
    return n;
end$$$

drop function if exists testOddRaiseError2$$$
                    
create function testOddRaiseError2(n int) returns int
not deterministic modifies sql data
begin
    return testOddRaiseError(n);
end$$$

drop function if exists testOddRaiseError3$$$
                    
create function testOddRaiseError3(n int) returns int
not deterministic modifies sql data
begin
    if (n % 4 = 0) then 
        call raiseError1('testcode', n, ' div 4 = 0');
        insert test(value) values(n * 4);
    end if;
    return testOddRaiseError2(n);
end$$$

delimiter ;


-- -------------
-- VERSION procs 2
-- -------------


delimiter $$$

drop function if exists __raiseError$$$
drop procedure if exists __raiseError$$$

create procedure __raiseError(msg varchar(128))
begin
    set @__errorMessage = msg;
    call `procedure that fails`();
end$$$


drop function if exists raiseError$$$
drop procedure if exists raiseError$$$

create procedure raiseError(msg varchar(128))
begin
    call __raiseError(substring(msg, 1, 128));
end$$$

drop function if exists raiseError1$$$
drop procedure if exists raiseError1$$$

create procedure raiseError1(error_code varchar(30), subject varchar(30), msg varchar(65))
begin
    call raiseError(concat_ws('|', coalesce(error_code, '<empty>'), coalesce(subject, ''), coalesce(msg, '')));
end$$$

drop function if exists raiseError2$$$
drop procedure if exists raiseError2$$$

create procedure raiseError2(
    error_code varchar(30), 
    subject1 varchar(30), 
    subject2 varchar(30), 
    msg varchar(35)
)
begin 
    call raiseError(concat_ws('|', 
                    coalesce(error_code, '<empty>'), 
                    coalesce(subject1, ''), 
                    coalesce(subject2, ''), 
                    coalesce(msg, '')));
end$$$

drop function if exists testOddRaiseError$$$
                    
create function testOddRaiseError(n int) returns int
not deterministic modifies sql data
begin
    if (n % 2 = 1) then 
        call raiseError1('testcode', n, ' test message');
        insert test(value) values(n * 2);
    end if;
    insert test(value) values(n);
    return n;
end$$$

delimiter ;