Reusable SQL in MySQL: Views, Stored Procedures, and User-Defined Functions

In SQL Real-World Scenarios and Part 6 on Subqueries and CTEs, every query I wrote was still temporary: as soon as a SELECT statement finished running, any CTE or derived table disappeared with it.

To wrap up my hands-on SQL practice, I learned the three tools databases give you for saving SQL logic permanently so you don’t have to rewrite it every time: Views, Stored Procedures, and User-Defined Functions. At first glance they sound almost identical, but they are built for three very different jobs.

1. Views: A Saved Query You Can Query Like a Table

Back when I practiced removing duplicate rows with ROW_NUMBER(), I used a CTE (WITH cte_table AS (...)) to filter the customers table down to unique IDs. The catch with a CTE is that it only lives for that single query. If ten different reports need that clean, deduplicated customers list, you would have to copy and paste the CTE ten times.

A View solves this by saving a SELECT query inside the database under a name so you can query it just like a regular table:

CREATE OR REPLACE VIEW dedup AS
WITH cte_table AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS 'number' 
    FROM customers
)
SELECT id, name, email FROM cte_table WHERE number = 1 ORDER BY id DESC;

Once the dedup view is created, pulling the clean customer list is a one-liner:

SELECT * FROM dedup;

Two important details about how standard MySQL views work:

• A view does not store a frozen copy of the data. It saves the query definition. Every time you run SELECT * FROM dedup;, MySQL executes the underlying CTE against the live customers table. If a new row (or a new duplicate) is inserted into customers, SELECT * FROM dedup; reflects it immediately.
• Using CREATE OR REPLACE VIEW instead of plain CREATE VIEW is a great habit. If the dedup view already exists and you want to tweak its query, CREATE OR REPLACE updates it in place instead of throwing an error that forces you to DROP VIEW dedup first.

2. Stored Procedures: Reusable Blocks of SQL Actions

While a view is a saved SELECT query that acts like a virtual table, a Stored Procedure is a saved block of SQL statements that you execute with CALL. Unlike views, stored procedures accept parameters and can modify data (INSERT, UPDATE, DELETE) or run multiple statements in sequence.

Here is the first stored procedure I wrote in MySQL to insert a new customer:

DELIMITER //
CREATE PROCEDURE first_procedure(IN p_id INT, IN p_name CHAR(100), p_email CHAR(100))
BEGIN 
    INSERT INTO customers
    VALUES (p_id, p_name, p_email);
END //
DELIMITER ;

Because the syntax around DELIMITER and IN looks strange the first time you see it, here is what each piece is doing:

Why DELIMITER // Is Required in MySQL

By default, MySQL uses the semicolon (;) to know when a statement ends and should be executed. Inside BEGIN ... END, however, the statements inside the procedure (INSERT INTO customers VALUES (...);) also end with semicolons.

If you didn’t change the delimiter first, MySQL would see the semicolon at the end of INSERT INTO customers ...; and think your CREATE PROCEDURE statement was finished before it ever reached END, resulting in a syntax error.

• DELIMITER // temporarily tells MySQL: “Ignore semicolons and wait until you see // to execute the whole block.”
• END // marks the end of the CREATE PROCEDURE command.
• DELIMITER ; switches the statement terminator right back to the normal semicolon.

Understanding Procedure Parameters (IN)

The procedure signature first_procedure(IN p_id INT, IN p_name CHAR(100), p_email CHAR(100)) defines three parameters:

• IN means the parameter is input-only: the caller passes a value into the procedure, and the procedure uses it.
• Notice that p_email CHAR(100) does not explicitly say IN in front of it. In MySQL, IN is the default parameter mode, so p_email is automatically treated as an IN parameter too (MySQL also supports OUT and INOUT parameters when a procedure needs to pass values back to the caller).

Calling a Stored Procedure

A stored procedure doesn’t run inside a SELECT statement; you trigger it explicitly using CALL:

CALL first_procedure(501, 'Niraj', 'niraj@example.com');

That single CALL runs the INSERT inside first_procedure, substituting 501, 'Niraj', and 'niraj@example.com' into p_id, p_name, and p_email.

3. User-Defined Functions: Custom Calculations Inside SELECT

A User-Defined Function (UDF) is also saved, reusable SQL logic that takes parameters, but with a strict contract: a function must return a single scalar value (RETURNS <type>) and is designed to be called directly inside expressions like SELECT or WHERE, just like built-in functions (ROUND(), UPPER(), or DATEDIFF()).

Here is a simple custom function that squares an integer:

DELIMITER //
CREATE FUNCTION square_it(x INT)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN 
    RETURN x * x;
END //
DELIMITER ;

Let’s break down the parts right below the function name:

• square_it(x INT): Accepts one integer input x.
• RETURNS INT: Declares that the function always returns a single INT value.
• DETERMINISTIC: Tells MySQL that for a given input x, this function will always produce the exact same output (square_it(4) is always 16). By contrast, a function that calls NOW() or RAND() would be NOT DETERMINISTIC.
• NO SQL: Declares that this function performs pure math and does not read or modify any tables.

(Why include DETERMINISTIC and NO SQL? When binary logging is enabled in MySQL, which is the default in MySQL 8+, MySQL refuses to create a function unless you explicitly declare its determinism and SQL data-access characteristics such as NO SQL or READS SQL DATA. Leaving those lines out is the #1 reason CREATE FUNCTION fails with Error 1418.)

Using the Function Inside SELECT

Once created, square_it() works inline inside any SELECT query just like a built-in SQL function:

SELECT unit_price, square_it(unit_price) FROM dim_product;

MySQL evaluates square_it(unit_price) row by row across dim_product and returns the squared price right next to the original unit_price.

Views vs. Stored Procedures vs. Functions: How to Tell Them Apart

Getting clear on the differences between these three took me a minute, so here is the side-by-side comparison I use to keep them straight:

Feature	View	Stored Procedure	User-Defined Function
What it saves	A single SELECT query	One or more SQL statements (actions)	A calculation that returns 1 value
How you run it	SELECT * FROM view_name;	CALL proc_name(args);	Inline inside SELECT fn_name(col)
Takes parameters?	No	Yes (IN, OUT, INOUT)	Yes (input arguments only)
What it returns	A virtual table (rows & columns)	Zero or more result sets / output params	Exactly one scalar value (RETURNS)
Can it INSERT / UPDATE / DELETE?	No (read-only virtual table)	Yes	No (meant for inline expressions)

The shortest rule of thumb I landed on:

• If I want to look at a filtered or joined dataset repeatedly -> View
• If I want to perform an action (insert, update, run a multi-step job) -> Stored Procedure
• If I want to compute a value per row inside a SELECT query -> Function

Wrapping Up My SQL Series (and What’s Next)

With views, stored procedures, and user-defined functions, SQL stopped feeling like just a way to ask questions of a table and started feeling like a complete programming environment with parameters, reusable modules, and return values.

This post marks the completion of my initial SQL learning track, from setting up MySQL in Docker and writing my first CREATE TABLE all the way to window functions, CTEs, views, and stored procedures. I wrote a short reflection in my Builds & Stories blog on finishing this SQL track and starting Database Design & Data Modeling next.