Creating Stored Procedures

Master MySQL CREATE PROCEDURE — the basic syntax, DELIMITER usage, returning result sets, SELECT INTO for variable assignment, the CALL statement, DROP PROCEDURE IF EXISTS for safe deployment, and the full mysql CLI workflow. Covers procedures with no parameters and procedures with local variables.

The CREATE PROCEDURE statement defines a new stored procedure. The basic form:

Key points:

• sp_name — the name you'll use to CALL the procedure. It must be unique within the database.
• (parameter_list) — zero or more parameters separated by commas. Use () for no parameters. Each parameter has an optional mode (IN default, or OUT/INOUT), a name, and a data type.
• BEGIN ... END — the procedure body. Can contain anything: SELECTs, DML, control flow, transactions, cursor loops.
• DELIMITER $$ — temporarily switches the statement terminator from ; to $$ so the inner semicolons don't end the CREATE early. DELIMITER ; at the end restores it.

The examples on this page use a familiar employees table. Run this once if it's not already loaded:

CREATE DATABASE IF NOT EXISTS gowtham;
USE gowtham;

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id     INT UNSIGNED PRIMARY KEY,
    first_name      VARCHAR(20),
    last_name       VARCHAR(25)    NOT NULL,
    salary          DECIMAL(10, 2) NOT NULL,
    department_id   INT
);

INSERT INTO employees VALUES
    (100, 'Steven',    'King',      24000.00,  9),
    (101, 'Neena',     'Kochhar',   17000.00,  9),
    (102, 'Lex',       'De Haan',   17000.00,  9),
    (103, 'Alexander', 'Hunold',     9000.00,  6),
    (104, 'Bruce',     'Ernst',      6000.00,  6),
    (105, 'David',     'Austin',     4800.00,  6),
    (106, 'Valli',     'Pataballa',  4800.00,  6),
    (107, 'Diana',     'Lorentz',    4200.00,  6),
    (108, 'Nancy',     'Greenberg', 12000.00, 10),
    (109, 'Daniel',    'Faviet',     9000.00, 10);

Verify with a quick SELECT:

SELECT employee_id, first_name, last_name, salary, department_id
FROM   employees
ORDER  BY first_name;

Wrap that SELECT in a procedure that any application can call:

DROP PROCEDURE IF EXISTS GetEmployees;

DELIMITER $$

CREATE PROCEDURE GetEmployees()
    READS SQL DATA
    COMMENT 'Return all employees ordered by first name'
BEGIN
    SELECT employee_id, first_name, last_name, salary, department_id
    FROM   employees
    ORDER  BY first_name;
END$$

DELIMITER ;

-- Run it
CALL GetEmployees();

Notice the procedure returns a full result set just like a regular SELECT. That's procedures' big difference from functions: procedures can return result sets, functions can't.

Count the employees earning at least 17,000, store the count in a local variable, then return it:

DROP PROCEDURE IF EXISTS GetTotalEmp;

DELIMITER $$

CREATE PROCEDURE GetTotalEmp()
    READS SQL DATA
BEGIN
    DECLARE total_emp INT DEFAULT 0;

    SELECT COUNT(*)
    INTO   total_emp
    FROM   employees
    WHERE  salary >= 17000;

    SELECT total_emp;
END$$

DELIMITER ;

CALL GetTotalEmp();

Three employees — Steven King, Neena Kochhar, Lex De Haan — earn at least 17,000.

Once created, run the procedure with CALL:

• Use CALL proc() for procedures with no parameters — note the empty parentheses are required.
• For procedures with parameters, supply literals or user variables (@x) in order. Local procedure variables can't be used here — only at the next level up.
• Procedures that SELECT something send the result set straight back to the client.
• Procedures with OUT / INOUT parameters need user variables (@total) so the caller can read them after the call.

DROP PROCEDURE removes a procedure from the database:

Always include IF EXISTS in deployment scripts — without it, dropping a non-existent procedure throws an error and aborts the script:

DROP PROCEDURE IF EXISTS GetTotalEmp;

If you're working in the command line, the full session looks like this:

Notice the prompt changes from mysql> to -> on continuation lines while you're typing a multi-line statement. The statement only executes when MySQL sees the current delimiter — that's why we need $$ at the end of END$$.

1. Always use DROP PROCEDURE IF EXISTS at the top of your CREATE script. Makes deployments idempotent.
2. Declare characteristics like READS SQL DATA, MODIFIES SQL DATA, DETERMINISTIC. Self-documenting and helps the optimizer.
3. Add a COMMENT describing what the procedure does. Visible in SHOW CREATE PROCEDURE later.
4. Use descriptive names with consistent casing — get_employees or GetEmployees, not sp1. Pick a convention and stick to it.
5. Default to SELECT INTO for single-row variable assignments. SET x = (SELECT ...) works too but is less idiomatic.
6. Handle the no-rows case. A SELECT INTO that returns no rows raises NOT FOUND — declare a handler or check first.
7. Use LIMIT 1 on SELECT INTO when the query might return multiple rows; otherwise you'll hit error 1172.

• CREATE PROCEDURE name(params) BEGIN ... END defines a stored procedure. Wrap it in DELIMITER $$ / DELIMITER ; when running through the mysql CLI.
• Procedures can return result sets via embedded SELECT statements — that's their big difference from stored functions.
• SELECT col INTO var FROM ... assigns a query result to a local variable. Must return exactly one row.
• Invoke with CALL procedure_name(args); — empty parentheses required even for no-parameter procedures.
• DROP PROCEDURE IF EXISTS name; removes a procedure safely. Note: no parentheses on the name.
• Add characteristics like READS SQL DATA, MODIFIES SQL DATA, DETERMINISTIC, COMMENT. They document the procedure and help the optimizer.