SQL PROCEDURE

What is SQL PROCEDURE?

A PROCEDURE is a stored program in the database that encapsulates SQL statements and control-flow logic, executed with a single call.

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 PROCEDURE: MySQL, MariaDB, PostgreSQL 11+, SQL Server, Oracle, Snowflake, DB2. Not supported in SQLite (uses triggers)

SQL PROCEDURE Full Explanation

PROCEDURE represents a reusable, named block of SQL and procedural code that is stored inside the database catalog. Like functions, procedures allow developers to centralize business logic close to the data, reduce duplication, and improve performance by avoiding repeated parsing and network round-trips. A procedure can accept input, output, or input-output parameters, perform complex operations (loops, conditions, transactions), and optionally return status information. Unlike functions, procedures typically do not return a value in an expression context; they are invoked for their side effects using CALL (MySQL, PostgreSQL) or EXEC/EXECUTE (SQL Server, Oracle).Behavior varies by dialect:- MySQL and MariaDB support CREATE PROCEDURE with deterministic and SQL-security modifiers.- SQL Server implements stored procedures with CREATE PROCEDURE and supports T-SQL.- PostgreSQL introduced CREATE PROCEDURE in version 11; before that, developers used CREATE FUNCTION with "RETURNS void".- Oracle PL/SQL uses CREATE PROCEDURE inside a PL/SQL block or package.Important caveats:- Permissions: executing or altering a procedure requires explicit privileges (e.g., EXECUTE, ALTER ROUTINE).- Parameter style and delimiter characters differ between dialects.- Debugging and error-handling mechanisms (e.g., EXCEPTION, TRY...CATCH) are language specific.- Recursive or long-running procedures can lock resources if not carefully coded.

SQL PROCEDURE Syntax

-- MySQL / PostgreSQL 11+
CREATE PROCEDURE procedure_name (
    IN  p_customer_id INT,
    OUT p_total_orders INT
)
LANGUAGE SQL
BEGIN
    SELECT COUNT(*) INTO p_total_orders
    FROM orders
    WHERE customer_id = p_customer_id;
END;

-- Invoke
CALL procedure_name(42, @total);
SELECT @total;

-- SQL Server
CREATE PROCEDURE dbo.GetTotalOrders
    @CustomerID INT,
    @TotalOrders INT OUTPUT
AS
BEGIN
    SELECT @TotalOrders = COUNT(*)
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
GO

EXEC dbo.GetTotalOrders @CustomerID = 42, @TotalOrders = @t OUT;

SQL PROCEDURE Parameters

procedure_name (identifier) - Name of the stored procedure.
parameter_list (list) - Zero or more IN, OUT, or INOUT/OUTPUT parameters with data types.
routine_body (block) - SQL and control-flow statements executed when the procedure is called.
characteristics (optional) - Modifiers such as LANGUAGE, DETERMINISTIC, SQL SECURITY. If not supported, specify None for that dialect.

Example Queries Using SQL PROCEDURE

-- Example 1: MySQL procedure to log user sign-in
CREATE PROCEDURE log_sign_in(IN p_user_id INT)
BEGIN
    INSERT INTO sign_in_log(user_id, sign_in_time)
    VALUES (p_user_id, NOW());
END;

CALL log_sign_in(101);

-- Example 2: SQL Server procedure to adjust salary
CREATE PROCEDURE dbo.RaiseSalary
    @EmpID INT,
    @Percent DECIMAL(5,2)
AS
BEGIN
    UPDATE Employees
    SET Salary = Salary * (1 + @Percent/100.0)
    WHERE EmployeeID = @EmpID;
END;
GO

EXEC dbo.RaiseSalary 7, 5.5;

Expected Output Using SQL PROCEDURE

The database stores the procedure definition in its catalog
When CALL/EXEC executes, the contained SQL runs atomically, affecting rows and returning any OUT/OUTPUT parameters or result sets

Use Cases with SQL PROCEDURE

Encapsulate frequently executed multi-step business logic (e.g., month-end closing).
Enforce data-integrity rules beyond declarative constraints.
Reduce application code by moving heavy data processing into the database.
Provide controlled, parameterized access for reporting or API back-ends.
Batch operations such as bulk inserts, updates, and maintenance tasks.

Common Mistakes with SQL PROCEDURE

Forgetting to grant EXECUTE privilege to end users.
Using SELECT ... INTO in MySQL when multiple rows are returned, causing runtime errors.
Omitting a DELIMITER change in MySQL client, resulting in premature statement termination.
Expecting a return value in SQL expressions; procedures must be called, not selected from.
Failing to handle transactions inside the procedure, leading to partial updates.

First Introduced In

SQL:1999 (SQL/PSM); MySQL 5.0

Frequently Asked Questions

What permissions are required to run a procedure?

You need the EXECUTE privilege on the procedure or the database. If the procedure performs data changes, you also need privileges on the underlying tables unless the procedure is defined with SQL SECURITY DEFINER (MySQL) or executed as a signed module (SQL Server).

Can a procedure return multiple result sets?

Yes. In MySQL and SQL Server you can issue multiple SELECT statements inside the body, and each produces its own result set that the client can consume sequentially.

How do I modify an existing procedure?

Use ALTER PROCEDURE where supported (SQL Server) or drop and recreate the procedure with CREATE OR REPLACE (PostgreSQL) or DROP PROCEDURE followed by CREATE PROCEDURE (MySQL).

Are procedures transactional?

They run within the caller's transaction context unless they start or end transactions explicitly. In MySQL you can include START TRANSACTION and COMMIT inside the procedure; in SQL Server you use BEGIN TRANSACTION. Always handle rollbacks to avoid partial updates.