MySQL Stored Procedures & User Defined Functions Quick Reference

MySQL Stored Procedures & User Defined Functions Quick Reference.md

1. MySQL Stored Procedures & User Defined Functions Quick Reference, by Gpt

1. Stored Procedures

Create Procedure

DELIMITER $$

CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] param1 datatype, ...)
BEGIN
    -- SQL statements
END$$

DELIMITER ;

Example:

DELIMITER $$

CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END$$

DELIMITER ;

Call Procedure

CALL procedure_name([param1, ...]);

Example:

CALL GetAllUsers();

Parameters

• IN (default): Input only
• OUT: Output only
• INOUT: Input and output

Example:

CREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
    SET sum = a + b;
END;

Variables

DECLARE var_name datatype [DEFAULT value];
SET var_name = value;

Control Flow

• IF:

  IF condition THEN
      -- statements
  ELSEIF condition THEN
      -- statements
  ELSE
      -- statements
  END IF;

• CASE:

  CASE var
      WHEN value1 THEN -- statements
      WHEN value2 THEN -- statements
      ELSE -- statements
  END CASE;

• LOOP/WHILE/REPEAT:

  [label:] LOOP ... END LOOP [label];
  [label:] WHILE condition DO ... END WHILE [label];
  [label:] REPEAT ... UNTIL condition END REPEAT [label];

Cursors

DECLARE cursor_name CURSOR FOR SELECT_statement;
OPEN cursor_name;
FETCH cursor_name INTO var1, var2, ...;
CLOSE cursor_name;

Error Handling

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    -- statements;

Drop Procedure

DROP PROCEDURE [IF EXISTS] procedure_name;

---

2. User Defined Functions (UDFs)

Create Function

DELIMITER $$

CREATE FUNCTION function_name(param1 datatype, ...) 
RETURNS datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | NO SQL | CONTAINS SQL]
BEGIN
    -- SQL statements
    RETURN value;
END$$

DELIMITER ;

Example:

DELIMITER $$

CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a + b;
END$$

DELIMITER ;

Call Function

SELECT function_name([param1, ...]);

Example:

SELECT AddNumbers(2, 3);

Notes

• Functions must return a value using RETURN.
• Functions cannot modify data (no INSERT, UPDATE, DELETE).
• Functions cannot use CALL, COMMIT, ROLLBACK, or dynamic SQL.

Drop Function

DROP FUNCTION [IF EXISTS] function_name;

---

3. Useful Information

• List Procedures/Functions:

  SHOW PROCEDURE STATUS [LIKE 'pattern'];
  SHOW FUNCTION STATUS [LIKE 'pattern'];

• Show Create:

  SHOW CREATE PROCEDURE procedure_name;
  SHOW CREATE FUNCTION function_name;

• Change Delimiter:
  Use DELIMITER $$ to avoid conflicts with ; inside procedure/function bodies.

---

4. Example: Procedure with IN/OUT

DELIMITER $$

CREATE PROCEDURE MultiplyNumbers(IN a INT, IN b INT, OUT result INT)
BEGIN
    SET result = a * b;
END$$

DELIMITER ;

CALL MultiplyNumbers(4, 5, @output);
SELECT @output;

---

5. Example: Simple Function

DELIMITER $$

CREATE FUNCTION Square(x INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN x * x;
END$$

DELIMITER ;

SELECT Square(6); -- Returns 36

---

6. Returning Tables (Result Sets)

Stored Procedures Returning Result Sets

Stored procedures in MySQL can return result sets (tables) directly using a SELECT statement.

Example:

DELIMITER $$

CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END$$

DELIMITER ;

Usage:

CALL GetAllUsers();

• The result of the SELECT inside the procedure is returned as a result set (table) to the caller.
• You can have multiple SELECT statements to return multiple result sets.

---

User Defined Functions Returning Tables

MySQL UDFs (scalar functions) cannot return a result set (table).
They must return a single value (scalar, e.g., INT, VARCHAR, etc.).

Workarounds

• Views: If you want a reusable "table" result, use a VIEW.
• Stored Procedures: Use a stored procedure if you need to return a table.
• JSON: In MySQL 5.7+ you can return a JSON array from a function, but not a true table.

Example: Function Returning JSON

DELIMITER $$

CREATE FUNCTION GetUserEmails()
RETURNS JSON
DETERMINISTIC
BEGIN
    RETURN (SELECT JSON_ARRAYAGG(email) FROM users);
END$$

DELIMITER ;

Usage:

SELECT GetUserEmails();

---

7. Summary Table

Feature	Stored Procedure	User Defined Function
Return Table (Result Set)	✅ Yes (via SELECT)	❌ No (only scalar)
Return Scalar Value	✅ Yes (via OUT param)	✅ Yes (via RETURN)
Use in SELECT clause	❌ No	✅ Yes

---

8. Best Practices

• To return a table: Use a stored procedure with a SELECT statement.
• To return a single value: Use a function.
• To use in a SELECT statement: Use a function (but only for scalar values).

---

9. Advanced: Returning Dynamic Result Sets

If you need to return different columns or tables, you can use dynamic SQL in stored procedures:

DELIMITER $$

CREATE PROCEDURE GetTableRows(IN tbl_name VARCHAR(64))
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', tbl_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

Usage:

CALL GetTableRows('users');

---

In summary:

• Stored procedures can return tables (result sets) via SELECT.
• Functions cannot return tables, only scalar values (or JSON as a workaround).

---

Tip:
Always use DELIMITER when creating procedures/functions to avoid syntax errors.

---

---

---

2. MySQL Stored Routines: Complete Quick Reference, by Gemini

This guide covers the two types of stored routines in MySQL and how to use them to return single values or entire result sets (tables).

1. Stored Procedures: Pre-compiled SQL statements that can be executed by CALLing them. They can have IN, OUT, and INOUT parameters and can return result sets to the client.
2. User-Defined Functions (UDFs): Pre-compiled routines that must return a single scalar value. They are used directly in SQL queries like built-in functions.

---

Part 1: Core Concepts & Syntax

Procedures vs. Functions

Feature	Stored Procedure	User-Defined Function
Invocation	CALL procedure_name();	SELECT function_name(); (Used in expressions)
Return Value	No direct return value. Uses OUT/INOUT parameters or sends a result set to the client.	Must return a single scalar value (e.g., INT, VARCHAR).
Parameters	IN, OUT, INOUT	Only IN parameters are supported.
Usage	Can execute DML (INSERT, UPDATE, DELETE), DDL, transactions, and return result sets.	Primarily for calculations. Cannot perform DML. Cannot use transactions.
Purpose	To perform complex business logic, transactions, or a series of actions.	To encapsulate a calculation or formula for reuse in queries.

The DELIMITER Command

This is essential when creating routines from a command-line client. It changes the end-of-statement character from ; to something else (commonly $$ or //), allowing the routine's body, which contains semicolons, to be passed to the server as a single statement.

-- Change the delimiter
DELIMITER $$

CREATE PROCEDURE ...
BEGIN
  -- Your code with semicolons here;
END$$

-- Change it back to the default
DELIMITER ;

---

Part 2: Stored Procedures

1. Syntax: CREATE PROCEDURE

CREATE PROCEDURE procedure_name ([parameter_mode] parameter_name data_type, ...)
[CHARACTERISTICS]
BEGIN
    -- Variable declarations
    -- SQL statements and logic
END

• parameter_mode:
  • IN: (Default) Pass a value into the procedure.
  • OUT: Pass a variable out of the procedure. Its initial value inside is NULL.
  • INOUT: Pass a variable in and out. The procedure can read and modify it.

2. Example: Procedure with OUT Parameter

This procedure finds the number of employees in a department and returns the count via an OUT parameter.

DELIMITER $$

CREATE PROCEDURE GetEmployeeCountByDept(
    IN p_dept_id INT,
    OUT p_employee_count INT
)
BEGIN
    SELECT COUNT(*)
    INTO p_employee_count
    FROM employees
    WHERE department_id = p_dept_id;
END$$

DELIMITER ;

3. Calling a Procedure

You use the CALL statement and pass session variables for OUT and INOUT parameters.

-- Declare a session variable to hold the output
SET @dept_id = 10;
SET @count = 0;

-- Call the procedure
CALL GetEmployeeCountByDept(@dept_id, @count);

-- See the result
SELECT @count AS 'Employee Count';

---

Part 3: User-Defined Functions (UDFs)

1. Syntax: CREATE FUNCTION

CREATE FUNCTION function_name (parameter_name data_type, ...)
RETURNS return_data_type
[CHARACTERISTICS]
BEGIN
    -- Variable declarations
    -- Logic (must have a RETURN statement)
END

• RETURNS return_data_type: This is mandatory.
• CHARACTERISTICS: Common ones are:
  • DETERMINISTIC: Always produces the same result for the same inputs.
  • NOT DETERMINISTIC: (Default) Result can change even with the same inputs.
  • READS SQL DATA: The function reads data from tables but does not modify it.

2. Example: Function Returning a Single Value

This function takes a date of birth and calculates the person's current age.

DELIMITER $$

CREATE FUNCTION CalculateAge(
    p_birth_date DATE
)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_birth_date, CURDATE());
END$$

DELIMITER ;

3. Calling a Function

Use it directly in SQL statements just like any built-in function.

-- Use it in a SELECT statement
SELECT first_name, last_name, CalculateAge(birth_date) AS age
FROM employees;

-- Use it in a WHERE clause
SELECT first_name, last_name
FROM employees
WHERE CalculateAge(birth_date) > 40;

---

Part 4: Returning Table-like Results (Result Sets)

A common requirement is to have a routine return a full table of data.

Key Point: MySQL functions cannot return a table. MySQL procedures can. This is the standard way to return a result set.

Here are the primary methods to achieve this.

Method 1: The Standard Approach (Procedure with a SELECT Statement)

Create a procedure that simply executes a SELECT query. The client application that CALLs the procedure will receive the rows as if it had run the query directly.

Example:

DELIMITER $$

CREATE PROCEDURE GetActiveUsers()
BEGIN
    -- This SELECT statement's result is what gets "returned" to the caller
    SELECT
        user_id,
        username,
        email,
        created_at
    FROM
        users
    WHERE
        is_active = 1
    ORDER BY
        username;
END$$

DELIMITER ;

How to Call:

CALL GetActiveUsers();
-- The client will receive a grid/table of the active users.

• Pros: Simple, clean, efficient, and the most idiomatic way to do this in MySQL.
• Cons: You cannot use the result directly in another SQL query (e.g., SELECT * FROM (CALL GetActiveUsers()) is invalid).

Method 2: The TEMPORARY TABLE Approach

Use this when you need to generate a result set and then use it in subsequent queries within the same session.

Example:

DELIMITER $$

CREATE PROCEDURE GenerateSalesReport(IN p_min_total DECIMAL(10, 2))
BEGIN
    -- Best practice: Drop the temp table if it already exists
    DROP TEMPORARY TABLE IF EXISTS temp_sales_report;

    -- Create the temporary table to hold the results
    CREATE TEMPORARY TABLE temp_sales_report (
        customer_id INT,
        customer_name VARCHAR(255),
        total_spent DECIMAL(10, 2)
    );

    -- Insert the complex query result into the temporary table
    INSERT INTO temp_sales_report (customer_id, customer_name, total_spent)
    SELECT c.id, c.name, SUM(o.amount) AS total
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
    HAVING total > p_min_total;
END$$

DELIMITER ;

How to Call (Two Steps):

-- Step 1: Call the procedure to create and populate the temporary table
CALL GenerateSalesReport(500.00);

-- Step 2: Now you can query the temporary table like a normal table
SELECT * FROM temp_sales_report ORDER BY total_spent DESC;

• Pros: The result is "composable" and can be used in JOINs or other queries.
• Cons: More complex; requires multiple statements from the client.

Method 3: The Modern JSON Approach

For applications and APIs, you can have a procedure return a single JSON string (via an OUT parameter) that contains your entire result set.

Example:

DELIMITER $$

CREATE PROCEDURE GetUsersAsJson(OUT p_users_json JSON)
BEGIN
    SELECT
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'userId', user_id,
                'username', username,
                'email', email
            )
        )
    INTO p_users_json
    FROM users WHERE is_active = 1;
END$$

DELIMITER ;

How to Call:

CALL GetUsersAsJson(@my_users);
SELECT @my_users;

• Pros: Returns a single, clean data structure, great for web APIs.
• Cons: Result is not directly usable in other SQL queries; client must parse the JSON.

Summary: Which Method to Use for Result Sets?

Goal	Recommended Method	Why?
Simple data retrieval for an application.	Method 1: Procedure with SELECT	It's the simplest, most direct, and most efficient method. It's the standard MySQL way.
Generate a dataset to JOIN or filter with other SQL.	Method 2: TEMPORARY TABLE	This is the only way to make the output "composable" within subsequent SQL statements.
Feed a web service/API that prefers JSON.	Method 3: Return JSON	Provides a clean, modern interface and decouples the client from the DB schema.
Save a complex SELECT by name (no logic needed).	Consider a VIEW instead.	A VIEW is a virtual table and is often simpler if you don't need procedural logic.

---

Part 5: Common Elements & Management

Common Logic Inside Routines

• Variables:
  • DECLARE my_var INT DEFAULT 0; (Must be at the top of a BEGIN...END block).
  • SET my_var = 10;
  • SELECT column INTO my_var FROM table WHERE ...; (Query must return 1 row).
• IF Statement: IF condition THEN ... ELSEIF ... ELSE ... END IF;
• CASE Statement: CASE ... WHEN ... THEN ... ELSE ... END CASE;
• Loops: WHILE, REPEAT, LOOP. Use LEAVE to exit and ITERATE to skip to the next iteration.
• Cursors: For iterating over multi-row result sets inside a procedure. The pattern is: DECLARE CURSOR, DECLARE HANDLER FOR NOT FOUND, OPEN, LOOP with FETCH, and CLOSE.
• Error Handling: DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLEXCEPTION | SQLSTATE '...'};

Managing Procedures and Functions

Action	Stored Procedure	User-Defined Function
Show Create Syntax	SHOW CREATE PROCEDURE procedure_name;	SHOW CREATE FUNCTION function_name;
List All Routines	SHOW PROCEDURE STATUS WHERE Db = 'db_name';	SHOW FUNCTION STATUS WHERE Db = 'db_name';
Drop (Delete)	DROP PROCEDURE [IF EXISTS] procedure_name;	DROP FUNCTION [IF EXISTS] function_name;