Stored Procedures & Functions

Saving logic on the database itself

A stored procedure is a named block of one or more SQL statements, saved inside the database, that you run by name with CALL. Think of it as a function — but living on the server. It can take parameters (inputs), so the same procedure works for any user, any city, any amount. Apps then call one tidy name instead of embedding long queries in their code.

A subtle point first: normally MySQL ends each statement at the semicolon ;. But a procedure contains semicolons inside it. So we temporarily change the “end of statement” marker with DELIMITER while we define the procedure, then change it back. Here is the full shape:

DELIMITER //

CREATE PROCEDURE add_user(IN p_name VARCHAR(100), IN p_city VARCHAR(100))
BEGIN
  INSERT INTO users (name, city) VALUES (p_name, p_city);
END //

DELIMITER ;

Walking through it: DELIMITER // tells MySQL “for now, a statement ends at //, not ;”, so the semicolons inside won’t end the definition early. CREATE PROCEDURE add_user(IN p_name ..., IN p_city ...) names the procedure and declares two input parameters (IN means a value you pass in). Everything between BEGIN and END is the body — here, a single INSERT using those parameters. END // finishes the procedure, and DELIMITER ; restores the normal semicolon.

Calling the procedure

Running it is the easy part — use CALL with the values for each parameter:

CALL add_user('Priya', 'Pune');
CALL add_user('Kabir', 'Delhi');

Each CALL runs the stored INSERT with the values you supply. The first adds Priya from Pune; the second adds Kabir from Delhi — the same saved logic, reused with different inputs. Your application never has to know the INSERT details; it just calls add_user by name.

OUT parameters and logic

Procedures can also return values through OUT parameters and contain real logic. This one counts how many users live in a given city and hands the answer back:

DELIMITER //

CREATE PROCEDURE count_in_city(IN p_city VARCHAR(100), OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count FROM users WHERE city = p_city;
END //

DELIMITER ;

CALL count_in_city('Pune', @total);
SELECT @total;

OUT p_count INT is an output slot the procedure fills in. SELECT COUNT(*) INTO p_count counts the matching users and stores the result in that slot. When we CALL it, we pass @total (a session variable) to receive the answer, then SELECT @total to see it. @total is a user variable — a temporary value that lasts for your connection.

Procedure vs function

MySQL also has stored functions, which are similar but with a key difference: a function returns a single value and is used inside a query (like a built-in), whereas a procedure is called on its own and can do several things. A tiny function example:

DELIMITER //

CREATE FUNCTION with_tax(p_price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(p_price * 1.18, 2);
END //

DELIMITER ;

SELECT title, with_tax(price) AS price_with_tax FROM products;

RETURNS DECIMAL(10,2) declares the type it gives back; DETERMINISTIC promises the same input always gives the same output. RETURN ROUND(p_price * 1.18, 2) is the value it produces. Because it returns one value, you can drop with_tax(price) straight into a SELECT like any built-in function — that is the big difference from a procedure.