#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL Stored Procedures Tutorial with Examples

Why keep SQL inside the database instead of in your application? Because some logic is shared by several programs, the website, the mobile app and the accounts team's Excel macro, and you want it written once, checked once, and run the same way every time. A stored procedure is a named block of SQL statements saved on the MySQL server and run with CALL. It can take parameters, use variables, IF conditions and loops, and handle errors.

This tutorial builds three procedures for a coaching institute's fee records, each one adding a new feature.

Setup

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(30),
  batch      VARCHAR(10),
  total_fee  DECIMAL(10,2),
  paid       DECIMAL(10,2) DEFAULT 0
);
CREATE TABLE payments (
  payment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  amount     DECIMAL(10,2),
  paid_on    DATE
);
INSERT INTO students VALUES
 (1,'Prakash','JAN-A',40000,10000),
 (2,'Zoya','JAN-A',40000,40000),
 (3,'Harsh','FEB-B',35000,0);

Procedure 1: an IN parameter that returns a result set

DELIMITER $$
CREATE PROCEDURE get_batch_dues(IN p_batch VARCHAR(10))
BEGIN
  SELECT name, total_fee - paid AS due
  FROM students
  WHERE batch = p_batch AND paid < total_fee
  ORDER BY due DESC;
END$$
DELIMITER ;

CALL get_batch_dues('JAN-A');
namedue
Prakash30000.00

Why DELIMITER? The body contains semicolons. Without changing the client's statement terminator to $$, the mysql client would send CREATE PROCEDURE ... to the server at the first ; and you would get a syntax error. DELIMITER is a client command, not SQL; GUI tools such as Workbench accept it in scripts, and application drivers do not need it at all.

Prefixing parameters with p_ is a habit worth keeping: a parameter named batch would be confused with the column batch, and MySQL resolves the name to the parameter, silently making the WHERE always true.

Procedure 2: an OUT parameter

DELIMITER $$
CREATE PROCEDURE count_defaulters(IN p_batch VARCHAR(10), OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count
  FROM students
  WHERE batch = p_batch AND paid < total_fee;
END$$
DELIMITER ;

CALL count_defaulters('JAN-A', @n);
SELECT @n AS defaulters;
defaulters
1

SELECT ... INTO stores a single value in a variable. The caller passes a session variable (@n) to receive it. There is also INOUT, a parameter that carries a value in and a changed value back out.

Procedure 3: variables, IF, a transaction and an error handler

Recording a payment touches two tables and must never half-succeed, so this procedure validates the input, wraps the writes in a transaction, and rolls back if any SQL error occurs.

DELIMITER $$
CREATE PROCEDURE record_payment(
    IN  p_student INT,
    IN  p_amount  DECIMAL(10,2),
    OUT p_status  VARCHAR(40))
BEGIN
  DECLARE v_due DECIMAL(10,2);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_status = 'ERROR: payment not recorded';
  END;

  SELECT total_fee - paid INTO v_due
  FROM students WHERE student_id = p_student;

  IF v_due IS NULL THEN
    SET p_status = 'Unknown student';
  ELSEIF p_amount <= 0 OR p_amount > v_due THEN
    SET p_status = CONCAT('Invalid amount, due is ', v_due);
  ELSE
    START TRANSACTION;
      INSERT INTO payments (student_id, amount, paid_on)
      VALUES (p_student, p_amount, CURRENT_DATE);
      UPDATE students SET paid = paid + p_amount WHERE student_id = p_student;
    COMMIT;
    SET p_status = CONCAT('OK, remaining due ', v_due - p_amount);
  END IF;
END$$
DELIMITER ;

Three calls, three outcomes:

CALL record_payment(1, 15000, @s); SELECT @s AS status;
CALL record_payment(1, 50000, @s); SELECT @s AS status;
CALL record_payment(9,  1000, @s); SELECT @s AS status;
callstatus
Prakash pays 15000OK, remaining due 15000.00
Prakash pays 50000Invalid amount, due is 15000.00
Student 9Unknown student
SELECT student_id, name, paid FROM students;
student_idnamepaid
1Prakash25000.00
2Zoya40000.00
3Harsh0.00

Only the valid payment was stored. Rules to notice: DECLARE statements must come first in a BEGIN ... END block (variables, then handlers); an EXIT handler leaves the block after running, while a CONTINUE handler carries on with the next statement. Without the handler, an error between the INSERT and UPDATE would leave the transaction open with a half-recorded payment (see COMMIT and ROLLBACK in MySQL).

Calling a procedure from application code

# Python, mysql-connector-python
cur = conn.cursor()
result = cur.callproc("record_payment", (1, 5000, ""))
print(result[2])        # OUT parameter value, e.g. 'OK, remaining due 10000.00'

Parameters are passed separately from the SQL text, so a procedure call is as safe from SQL injection as a prepared statement, provided the procedure itself does not build dynamic SQL from its inputs.

Managing procedures

SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW CREATE PROCEDURE record_payment;
DROP PROCEDURE IF EXISTS get_batch_dues;

MySQL has no CREATE OR REPLACE PROCEDURE, and ALTER PROCEDURE changes only characteristics such as the comment, not the body. To change the code, drop and re-create it. Keep the source in Git like any other code. Running a procedure needs the EXECUTE privilege; creating one needs CREATE ROUTINE.

When not to use a stored procedure

Procedures pair naturally with triggers (logic that fires on row changes) and scheduled events (logic that fires on a timer).

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →