Create sql procedure but it's not appeared in the table

Question

there is no error but also doesn't show success

at first, it shows on only table registrations and not the registration_details, and now doesn't appear in both table.

set foreign_key_checks = 0;
drop procedure if exists createRegist;

delimiter //

create procedure createRegist()
begin
    declare total_credit float;
    declare registration_id INT;
    declare credit float;
    
    -- create first registration for student 1
    set total_credit = 0;
    insert into `student_regist`.`registrations` (`registration_id`, `student_id`,`total_credit`)
        values (1, 1, total_credit);
    
SELECT LAST_INSERT_ID() INTO registration_id;
     
    -- create registration detail 1
SELECT 
    `student_regist`.`courses`.`credit`
INTO credit FROM
    `student_regist`.`courses`
WHERE
    `student_regist`.`courses`.`course_id` = 1
LIMIT 1;
    set total_credit = total_credit + credit;
    insert into `student_regist`.`registration_details` (`registration_details_id`, `registration_id`, `course_id`, `semester`) 
        values (1, 1, 1, 1);

    SELECT 'Success';
end//

delimiter ;

score 0 · Answer 1 · answered Jan 20 '23 at 09:54

You have not provided nearly enough detail for us to provide any concrete answers. Adding the DDL for your tables to your question is the minimum required for a real answer.

That said, here are some suggestions.

We know nothing of the values you need to store in credit (and total_credit) but it seems likely that it should be DECIMAL, and not FLOAT. Searching decimal vs float on here returns Float vs Decimal in ActiveRecord as the first result.

If you are using MySQL Workbench the errors/warnings should be displayed in the Output Area (View -> Panels -> Show Output Area). Or you could run SHOW WARNINGS; after calling your SP.

CALL createRegist();
SHOW WARNINGS;

Your first insert into registrations uses a hardcoded value of 1 for registration_id, which is presumably the primary key (PK) for the table. The second time you execute the SP and it tries to insert 1 into your PK, it will fail with a duplicate key error -

Error Code: 1062. Duplicate entry '1' for key 'registrations.PRIMARY'

You then follow up with the call for LAST_INSERT_ID() which will not work as you are expecting. From the MySQL docs -LAST_INSERT_ID()

The value of LAST_INSERT_ID() is not changed if you set the AUTO_INCREMENT column of a row to a non-“magic” value (that is, a value that is not NULL and not 0).

Changing the value passed in the insert statement to NULL or 0 (or removing completely) will resolve this -

/* Passing in value of NULL */
set total_credit = 0;
insert into `student_regist`.`registrations` (`registration_id`, `student_id`,`total_credit`)
    values (NULL, 1, total_credit);

/* or removing completely */
set total_credit = 0;
insert into `student_regist`.`registrations` (`student_id`,`total_credit`)
    values (1, total_credit);

Create sql procedure but it's not appeared in the table

1 Answers1