Member Avatar for wchitamb
wchitamb 0 Light Poster

Post moved

Edited by wchitamb because: n/a
  • 1 Contributor
  • 0 Replies
  • 91 Views

Dani AI

Generated

Thread moved by . Short, practical primer for creating MySQL stored procedures that insert rows, return generated IDs, and handle basic errors.

When defining a procedure in the MySQL client, change the statement delimiter so the body can contain semicolons. Creating routines requires the CREATE ROUTINE privilege and calling them requires EXECUTE. For auto_increment keys, use LAST_INSERT_ID() (session-scoped) and return it via an OUT parameter or a SELECT at the end of the procedure. To get transactional safety, use InnoDB and explicit START TRANSACTION / COMMIT with an error handler to ROLLBACK on failure.

Example (minimal, ready-to-adapt):

DELIMITER //
CREATE PROCEDURE insert_person(
  IN  p_name VARCHAR(100),
  IN  p_age  INT,
  OUT p_id   INT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_id = NULL;
  END;
  START TRANSACTION;
  INSERT INTO people(name, age) VALUES (p_name, p_age);
  SET p_id = LAST_INSERT_ID();
  COMMIT;
END //
DELIMITER ;
-- Call:
-- CALL insert_person('Alice', 30, @newid);
-- SELECT @newid;

Notes: use DECLARE handlers to surface predictable behavior, prefer OUT params for callers that need the new id, and avoid relying on MyISAM if transactions or rollbacks are required. For full reference and edge cases (DELIMITER rules, privileges, SIGNAL/RESIGNAL and handler syntax), consult the MySQL manual: Stored Programs and Views.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.