Basic Syntax

CREATE PROCEDURE procedure_name ()
BEGIN
  -- SQL statements go here
END;

Example: Insert a TokTuk User

This procedure adds a new user, taking their username and email as parameters:

CREATE PROCEDURE AddUser(IN uname TEXT, IN email TEXT)
BEGIN
  INSERT INTO Users (username, email)
  VALUES (uname, email);
END;

Calling a Stored Procedure

CALL AddUser('sofia', 'sofia@toktuk.com');

🎯 Just like calling a function — but in SQL.

Why Use Stored Procedures?

• Encapsulate logic (like billing calculations or status updates)
• Enforce business rules in one place
• Reduce repetition and simplify application code
• Improve security by controlling access to raw SQL

Using Transactions in a Procedure

Stored procedures can wrap statements in BEGIN/COMMIT blocks:

CREATE PROCEDURE SafeInsert(
  IN uid INT,
  IN uname TEXT,
  IN email TEXT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;

  START TRANSACTION;
    INSERT INTO Users (user_id, username, email)
    VALUES (uid, uname, email);
  COMMIT;
END;