Definition

A predefined, reusable set of SQL statements tailored to a specific task that executes when explicitly invoked.

Creation and Execution Syntax

2.1 Create a Stored Procedure

Lowercase syntax example:

create procedure proc_name([parameters])
begin
-- SQL statements block
end;

Uppercase syntax example:

CREATE PROCEDURE proc_name([parameters])
BEGIN
-- SQL statements block
end;

2.2 Execute a Stored Procedure

CALL proc_name([parameters]);

2.3 Parameter Details

1. Parameter Components: Parameter Mode Parameter Name Parameter Type (e.g., IN emp_name VARCHAR(25))
   • IN: Input-only; must receive a value from the caller
   • OUT: Output-only; returns a value to the caller
   • INOUT: Both input and output
2. BEGIN/END block are optional for single-statement procedures
3. Use DELIMITER to redefine SQL terminator temporarily (since MySQL defaults to ;)

Example Without Parameters

DELIMITER $$
CREATE PROCEDURE add_dummy_admins()
BEGIN
    INSERT INTO admins(login, `passcode`) VALUES ('ALICE_01', 'pwd123');
    INSERT INTO admins(login, `passcode`) VALUES ('BOB_02', 'pwd456');
    INSERT INTO admins(login, `passcode`) VALUES ('CHARLIE_03', 'pwd789');
    INSERT INTO admins(login, `passcode`) VALUES ('DAVID_04', 'pwd000');
    INSERT INTO admins(login, `passcode`) VALUES ('EMMA_05', 'pwd999');
END $$
DELIMITER ;

CALL add_dummy_admins();

Example with Input Parameterss

DELIMITER $$
CREATE PROCEDURE count_admin_match(IN login_name VARCHAR(25), IN pass_str VARCHAR(25))
BEGIN
    DECLARE total_count INT DEFAULT 0;
    SELECT COUNT(*) INTO total_count
    FROM admins
    WHERE admins.login = login_name
      AND admins.passcode = pass_str;
END $$
DELIMITER ;

CALL count_admin_match('11', '22');

Example with Output Parameters

DELIMITER $$
CREATE PROCEDURE get_boyfriend_info(IN girl_name VARCHAR(25), OUT bf_name VARCHAR(25), OUT charm_score VARCHAR(25))
BEGIN
    SELECT b.boy_name, b.charm_score INTO bf_name, charm_score
    FROM boys b
    INNER JOIN girls g ON b.id = g.boyfriend_id
    WHERE g.name = girl_name;
END $$
DELIMITER ;

CALL get_boyfriend_info('Xiaozhao', @boy_name, @cp_value);
SELECT @boy_name AS BoyName, @cp_value AS CharmScore;

Example with INOUT Parameters

DELIMITER $$
CREATE PROCEDURE double_two_nums(INOUT num1 INT, INOUT num2 INT)
BEGIN
    SET num1 = num1 * 2;
    SET num2 = num2 * 2;
END $$
DELIMITER ;

SET @var1 = 10;
SET @var2 = 30;
CALL double_two_nums(@var1, @var2);
SELECT @var1, @var2;