SQL Procedure "Result consisted of more than one row" error

I have the following procedure implemented in my database and I get "Result consisted of more than one row" when I run it. For the value I'm passing as 'domain_level_two_id' there's only one row inside 't_auto_generate_dml2_based_code_sequence' because it's the primary key of the table.

My procedure is as below:

create
    definer = dt@`%` procedure create_farmer_unique_code(IN domain_level_two_id int, OUT generated_unique_code varchar(50))
BEGIN

    DECLARE int_seq int;
    DECLARE dml2_code char(3);
    DECLARE error_message varchar(255);

    IF (SELECT COUNT(*) FROM t_auto_generate_dml2_based_code_sequence WHERE DOMAIN_LEVEL_TWO_ID = domain_level_two_id) =
       0 THEN
        SET error_message = CONCAT('Division ID ', domain_level_two_id, ' does not exist.');
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = error_message;
    END IF;

    UPDATE t_auto_generate_dml2_based_code_sequence
    SET CURRENT_VALUE = (CURRENT_VALUE + 1)
    WHERE DOMAIN_LEVEL_TWO_ID = domain_level_two_id;

    SELECT CURRENT_VALUE, DOMAIN_LEVEL_TWO_CODE
    INTO int_seq, dml2_code
    FROM t_auto_generate_dml2_based_code_sequence
    WHERE DOMAIN_LEVEL_TWO_ID = domain_level_two_id;

    SET generated_unique_code = CONCAT(dml2_code, '-', LPAD(int_seq, 5, '0'));

END;

I tried to run the procedure to get a unique farmer code.

Solution

I would suggest that you change the name of the function argument from domain_level_two_id to something else, such that it is not the same as the column name in t_auto_generate_dml2_based_code_sequence .

create
    definer = dt@`%` procedure create_farmer_unique_code(IN input_domain_level_two_id int, OUT generated_unique_code varchar(50))
BEGIN

    DECLARE int_seq int;
    DECLARE dml2_code char(3);
    DECLARE error_message varchar(255);

    IF (SELECT COUNT(*) FROM t_auto_generate_dml2_based_code_sequence WHERE DOMAIN_LEVEL_TWO_ID = input_domain_level_two_id) =
       0 THEN
        SET error_message = CONCAT('Division ID ', input_domain_level_two_id, ' does not exist.');
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = error_message;
    END IF;

    UPDATE t_auto_generate_dml2_based_code_sequence
    SET CURRENT_VALUE = (CURRENT_VALUE + 1)
    WHERE DOMAIN_LEVEL_TWO_ID = input_domain_level_two_id;

    SELECT CURRENT_VALUE, DOMAIN_LEVEL_TWO_CODE
    INTO int_seq, dml2_code
    FROM t_auto_generate_dml2_based_code_sequence
    WHERE DOMAIN_LEVEL_TWO_ID = input_domain_level_two_id;

    SET generated_unique_code = CONCAT(dml2_code, '-', LPAD(int_seq, 5, '0'));

END;

Something along these lines.

I am not entirely sure if it will work but it's worth a try.