SQL MODIFIES

What is the SQL MODIFIES SQL DATA clause?

Declares that a routine (procedure or function) is allowed to perform INSERT, UPDATE, DELETE, or other data-changing statements.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MODIFIES: MySQL, MariaDB, IBM Db2, Amazon Redshift (procedures), SQL Standard (SQL/PSM). Not supported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL MODIFIES Full Explanation

MODIFIES SQL DATA is a data-access classification used in the SQL/PSM standard and several relational databases to label stored procedures or functions that change persistent data. When you create a routine, you must specify one of four access levels: NO SQL (no SQL at all), READS SQL DATA (only queries), MODIFIES SQL DATA (may change data), or CONTAINS SQL (unspecified but uses SQL). This classification lets the optimizer, permission system, and replication tools understand potential side effects. A routine marked MODIFIES SQL DATA can execute INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DDL, or call other routines that do so. However, it is not implicitly granted write privileges; the routine owner still needs the necessary table permissions. Some dialects require MODIFIES SQL DATA for deterministic functions that update temporary tables, while others treat it as the default if no clause is given. Using the wrong clause may lead to runtime errors or blocked writes.

SQL MODIFIES Syntax

CREATE FUNCTION function_name (parameter_list)
RETURNS return_datatype
MODIFIES SQL DATA
BEGIN
   -- data-changing statements
END;

CREATE PROCEDURE procedure_name (parameter_list)
MODIFIES SQL DATA
BEGIN
   -- data-changing statements
END;

SQL MODIFIES Parameters

Example Queries Using SQL MODIFIES

-- Example 1: Function that logs and returns a value
CREATE FUNCTION add_employee(name VARCHAR(100), dept_id INT)
RETURNS INT
MODIFIES SQL DATA
BEGIN
   INSERT INTO employees(employee_name, department_id)
   VALUES(name, dept_id);
   RETURN LAST_INSERT_ID();
END;

-- Example 2: Procedure that archives old orders
CREATE PROCEDURE archive_old_orders(p_cutoff DATE)
MODIFIES SQL DATA
BEGIN
   INSERT INTO orders_archive
   SELECT * FROM orders WHERE order_date < p_cutoff;
   DELETE FROM orders WHERE order_date < p_cutoff;
END;

Expected Output Using SQL MODIFIES

The CREATE FUNCTION/PROCEDURE statement is stored successfully
When invoked, the routine can legally run data-modifying SQL statements and will return its defined result or complete without error

Use Cases with SQL MODIFIES

Writing ETL routines that load or transform data inside the database
Implementing business-logic functions that insert audit rows or update status flags
Grouping several DML statements into a single transaction-controlled procedure
Allowing replication or security frameworks to identify routines that alter data

Common Mistakes with SQL MODIFIES

Omitting the clause in dialects that require it, leading to an error when the routine performs DML
Using MODIFIES SQL DATA in a function that should be deterministic and side-effect-free, causing unexpected writes in queries
Assuming the clause grants INSERT/UPDATE privileges; the caller still needs underlying table permissions

First Introduced In

SQL:1999 (SQL/PSM)

Frequently Asked Questions

What does MODIFIES SQL DATA allow?

It allows a function or procedure to run any statement that changes persistent data, including INSERT, UPDATE, DELETE, MERGE, or DDL.

Is MODIFIES SQL DATA required in MySQL?

Yes. If a routine performs DML you must declare MODIFIES SQL DATA (or leave the clause out, which defaults to MODIFIES) or the server returns an error.

Can I call a MODIFIES SQL DATA routine from a SELECT?

You can call it, but the SELECT loses determinism and may be blocked in some configurations (e.g., read-only replicas). Use READS SQL DATA or NO SQL for pure queries.

Does the clause affect transaction handling?

No. It only describes potential side effects. You still control commits and rollbacks inside the routine or rely on implicit transactional behavior.