Stored Procedure in mySQL issues

For class I am to make a stored procedure named add_parts that will be used to add records to the parts_service table. It has 2 input parameters that match the part_id and parts_qty field/data types from that table. I have to also declare a variable that will hold onto the service_id value from another table services and set it to MAX using a select into. Here is the code I have:

/*Set DB context and drop the procedure if it exists (2 lines)*/
USE cf;
DROP PROCEDURE IF EXISTS add_parts;

/*Delimiter statement (1 line)*/
DELIMITER //

/*Create procedure statement & 2 int parameters.*/
CREATE PROCEDURE add_parts (
  IN part_id_param INT,
  IN parts_qty_param INT
)
BEGIN   

/*Declare the internal int variable (1 line)*/
DECLARE service_id_var INT;

/*Declare sql_error variable, Declare continue handler, set sql_error variable (3 lines)*/
DECLARE sql_error TINYINT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET sql_error = TRUE;

/*Set the internal int variable */
/*From the appropriate table (3 lines)*/
SELECT MAX(service_id) INTO service_id_var FROM service;

START TRANSACTION;

/*Insert into parts_service values statement */
INSERT INTO parts_service VALUES(DEFAULT, part_id, part_qty, service_id_var);

/* If/else conditional; if sql_error variable is false, commit the transaction and select the appropriate message*/
/* else, rollback the transaction and select the appropriate message */
IF sql_error = FALSE THEN 
  COMMIT;
  SELECT 'Record was added!' AS Message;
ELSE 
  ROLLBACK;
  SELECT 'The part id you entered does not exist' AS Message;
END IF;

END//

CALL add_parts(15,7);

The CALL in the last line should return "Record was added!" but instead returns "The part id you entered does not exist". How do I tell if I am doing this correctly?

CREATE TABLE parts_service (
parts_service_id int(11) NOT NULL AUTO_INCREMENT,
part_id int(11) NOT NULL,
service_id int(11) NOT NULL,
parts_qty int(11) DEFAULT NULL,
PRIMARY KEY (parts_service_id),
KEY ps_fk_parts (part_id),
KEY ps_fk_service (service_id),
CONSTRAINT ps_fk_parts FOREIGN KEY (part_id) REFERENCES parts (part_id),
CONSTRAINT ps_fk_service FOREIGN KEY (service_id) REFERENCES service (service_id)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

1 answer

  • answered 2019-11-14 04:54 WEBjuju

    Aha, these two are out of order:

    service_id int(11) NOT NULL,
    parts_qty int(11) DEFAULT NULL,
    

    The query (with constraint on the service table) must be failing when trying to insert the parts_qty to the service_id.

    Switch your insert to this:

    INSERT INTO parts_service VALUES(DEFAULT, part_id, service_id_var, part_qty);
    

    Alternatively, and this is probably better, you could explicitly set the columns your values are to be placed into:

    INSERT INTO parts_service 
      (`parts_service_id`, `part_id`, `parts_qty`, `service_id`) 
    VALUES
      (DEFAULT, part_id, part_qty, service_id_var);