#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

Functions vs Stored Procedures in MySQL: Differences

Both are named blocks of SQL stored on the server, and they serve different purposes. A stored function calculates and returns one value, so you use it inside a query the way you use ROUND() or CONCAT(). A stored procedure carries out a task, often several statements with a transaction, and you run it on its own with CALL. If you need a value inside a SELECT, write a function. If you need to change data in steps, write a procedure.

The differences at a glance

Stored functionStored procedure
How it runsInside an expression: SELECT f(x), WHERE f(x) > 0CALL p(...) as its own statement
ReturnsExactly one value via RETURNNothing, or values through OUT/INOUT parameters, or result sets
ParametersInput onlyIN, OUT, INOUT
TransactionsNot allowed (no COMMIT/ROLLBACK)Allowed
Returning a result setNot allowedAllowed (a plain SELECT inside it)
Dynamic SQL (PREPARE)Not allowedAllowed
Binary logging rulesMust declare DETERMINISTIC, NO SQL or READS SQL DATA (unless an admin relaxes it)No such requirement

Sample data

CREATE TABLE accounts (
  acc_id  INT PRIMARY KEY,
  holder  VARCHAR(20),
  balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);
CREATE TABLE transfers (
  tr_id    INT AUTO_INCREMENT PRIMARY KEY,
  from_acc INT, to_acc INT, amount DECIMAL(12,2),
  done_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts VALUES (1,'Kavya',25000), (2,'Nikhil',4000), (3,'Rahul',12000);

A function: GST on an amount

Bodies contain semicolons, so the mysql client needs a temporary delimiter. MySQL Workbench handles this for you.

DELIMITER //
CREATE FUNCTION gst_amount(base DECIMAL(12,2), rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
BEGIN
  RETURN ROUND(base * rate / 100, 2);
END //
DELIMITER ;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its
declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

Binary logging is on by default in MySQL 8.0, so MySQL wants to know how the function behaves before it accepts it. This one always gives the same output for the same input and reads no tables, so declare that:

DELIMITER //
CREATE FUNCTION gst_amount(base DECIMAL(12,2), rate DECIMAL(5,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL
BEGIN
  RETURN ROUND(base * rate / 100, 2);
END //
DELIMITER ;

SELECT gst_amount(1999, 18) AS gst, 1999 + gst_amount(1999, 18) AS total;
gsttotal
359.822358.82

Declare these honestly. Marking a function DETERMINISTIC when it is not (for example, if it uses NOW()) can make replicas drift from the source. A function can also read tables:

DELIMITER //
CREATE FUNCTION balance_band(p_acc INT) RETURNS VARCHAR(10)
READS SQL DATA
BEGIN
  DECLARE b DECIMAL(12,2);
  SELECT balance INTO b FROM accounts WHERE acc_id = p_acc;
  RETURN CASE WHEN b IS NULL THEN 'unknown' WHEN b >= 10000 THEN 'high' ELSE 'low' END;
END //
DELIMITER ;

SELECT holder, balance, balance_band(acc_id) AS band FROM accounts ORDER BY acc_id;
holderbalanceband
Kavya25000.00high
Nikhil4000.00low
Rahul12000.00high

Be careful with this pattern on big tables: the inner SELECT runs once per row. A join would usually be faster.

A procedure: transfer money safely

DELIMITER //
CREATE PROCEDURE transfer_money(IN p_from INT, IN p_to INT, IN p_amt DECIMAL(12,2),
                                OUT p_status VARCHAR(40))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_status = 'failed: rolled back';
  END;

  START TRANSACTION;
    UPDATE accounts SET balance = balance - p_amt WHERE acc_id = p_from;
    UPDATE accounts SET balance = balance + p_amt WHERE acc_id = p_to;
    INSERT INTO transfers (from_acc, to_acc, amount) VALUES (p_from, p_to, p_amt);
  COMMIT;
  SET p_status = 'ok';
END //
DELIMITER ;

CALL transfer_money(1, 2, 5000, @s);   SELECT @s AS status;
CALL transfer_money(2, 3, 50000, @s);  SELECT @s AS status;
status
ok
status
failed: rolled back
SELECT acc_id, holder, balance FROM accounts ORDER BY acc_id;
SELECT tr_id, from_acc, to_acc, amount FROM transfers;
acc_idholderbalance
1Kavya20000.00
2Nikhil9000.00
3Rahul12000.00
tr_idfrom_accto_accamount
1125000.00

The second transfer would have pushed Nikhil's balance below zero. The CHECK constraint raised an error, the handler rolled the whole transaction back, and the procedure reported failure through its OUT parameter. A function could not do this: try putting COMMIT in one and MySQL refuses with ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger. Transactions are explained in COMMIT and ROLLBACK explained.

Using one where the other belongs

SELECT transfer_money(1,2,10,@s);  -> ERROR 1305 (42000): FUNCTION fp.transfer_money does not exist
CALL gst_amount(100, 18);          -> ERROR 1305 (42000): PROCEDURE fp.gst_amount does not exist

Functions and procedures live in separate namespaces (here the database is fp), so the error message says "does not exist" even though a routine with that name does exist. List what you have with:

SELECT ROUTINE_NAME, ROUTINE_TYPE, IS_DETERMINISTIC, SQL_DATA_ACCESS
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'fp'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;
ROUTINE_NAMEROUTINE_TYPEIS_DETERMINISTICSQL_DATA_ACCESS
balance_bandFUNCTIONNOREADS SQL DATA
gst_amountFUNCTIONYESNO SQL
transfer_moneyPROCEDURENOCONTAINS SQL

Choosing

A procedure can call functions, and a procedure can be scheduled with MySQL events. Neither should be confused with triggers, which run automatically on inserts, updates and deletes. For a full procedure walkthrough, see the stored procedures tutorial.

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 →