SQL CREATE PROCEDURE

What does SQL CREATE PROCEDURE do?

Defines a named stored procedure that can be invoked repeatedly with optional parameters.

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 CREATE PROCEDURE: SQL Server, MySQL, MariaDB, PostgreSQL, Oracle, IBM Db2, Firebird. Not supported in SQLite.

SQL CREATE PROCEDURE Full Explanation

CREATE PROCEDURE is a Data Definition Language (DDL) statement that registers executable logic inside the database. A procedure encapsulates one or more SQL statements (and optional procedural code) so that they can be called by name, often with IN, OUT, or INOUT parameters. Stored procedures run on the server, close to the data, which can reduce network traffic, centralize business rules, and improve performance.Most products implement CREATE PROCEDURE as part of their procedural extensions (PL/pgSQL for PostgreSQL, T-SQL for SQL Server, PL/SQL for Oracle, etc.). Core behavior is similar: you supply a unique procedure name, optional parameter list, and a block of statements terminated with END (or language-specific keyword). Procedures can return data through OUT parameters, result sets (SELECT inside), or database-specific RETURN/RESULT constructs.Key points:- Requires appropriate privileges (e.g., CREATE ROUTINE in MySQL, CREATE PROCEDURE in SQL Server).- Stored in the system catalog until explicitly dropped.- May contain transactional control statements depending on dialect.- Parameter modes default to IN when not specified.- Changing an existing procedure usually requires DROP PROCEDURE then CREATE, unless ALTER PROCEDURE is supported.- In MySQL CLI you must change the statement delimiter so the body’s semicolons are not misinterpreted.

SQL CREATE PROCEDURE Syntax

-- Generic ISO/PSM style
CREATE PROCEDURE procedure_name [(param_mode param_name data_type [, ...])]
[LANGUAGE SQL | PLSQL | plpgsql | ...]
[DETERMINISTIC | NOT DETERMINISTIC]
[BEGIN]
    -- SQL and control statements
END;

-- SQL Server (T-SQL) example
CREATE PROCEDURE schema_name.procedure_name
    @param1 INT,
    @param2 NVARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    -- statements
END;
GO

SQL CREATE PROCEDURE Parameters

procedure_name (identifier) - Name of the procedure to create.
param_mode (keyword) - IN, OUT, or INOUT/OUTPUT indicating direction (default IN).
param_name (identifier) - Name of each parameter.
data_type (type) - Valid SQL data type for each parameter.
LANGUAGE (keyword) - Procedural language in which the body is written (dialect specific).
DETERMINISTIC (flag) - Indicates the routine always returns the same result for the same inputs (MySQL and others).
Body (block) - One or more SQL statements enclosed by BEGIN … END or dialect-specific wrapper.

Example Queries Using SQL CREATE PROCEDURE

-- MySQL style
DELIMITER $$
CREATE PROCEDURE add_order(IN p_user_id INT, IN p_total DECIMAL(10,2))
BEGIN
    INSERT INTO orders(user_id,total_amount,created_at)
    VALUES(p_user_id,p_total,NOW());
END$$
DELIMITER ;

-- SQL Server style
CREATE PROCEDURE dbo.GetRecentOrders @days INT = 7
AS
BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM orders
    WHERE created_at >= DATEADD(DAY,-@days,GETDATE());
END;
GO

-- PostgreSQL style
CREATE OR REPLACE PROCEDURE public.raise_salary(emp_id INT, percent NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + percent/100)
    WHERE id = emp_id;
END;
$$;

Expected Output Using SQL CREATE PROCEDURE

Procedure objects are stored in the database catalog
No rows are returned at creation time aside from a confirmation message (e
g
, Query OK, 0 rows affected)
Subsequent CALL or EXEC statements execute the packaged logic

Use Cases with SQL CREATE PROCEDURE

Encapsulate complex business logic so it runs close to data.
Reuse common data-modification routines (insert, update, cleanup).
Enforce validations or security checks before writes.
Batch operations that need to run on schedules (via jobs or cron).
Provide an abstraction layer for application developers.

Common Mistakes with SQL CREATE PROCEDURE

Forgetting to switch delimiter in MySQL clients, causing syntax errors.
Omitting parameter mode and assuming OUT when default is IN.
Using RETURN in dialects where procedures should use OUT params (MySQL).
Expecting implicit COMMIT/ROLLBACK when dialect runs procedure in caller’s transaction.
Not granting EXECUTE permissions to callers, leading to permission errors.

First Introduced In

SQL-99 (Persistent Stored Modules)

Frequently Asked Questions

What is the difference between a procedure and a function?

A procedure may return data via OUT parameters or result sets but does not have to return a scalar value, while a function must return a value and can often be used inside SELECT statements.

Can I use transactions inside a stored procedure?

Yes, most dialects allow BEGIN/COMMIT/ROLLBACK inside procedures, but behavior varies. SQL Server treats the procedure as part of the caller’s transaction unless a new one is started explicitly.

How do I debug a stored procedure?

Options include printing messages (RAISE NOTICE, PRINT), stepping through with an IDE debugger (SQL Server Management Studio, MySQL Workbench), or logging to a table.

Are stored procedures faster than application code?

They can be because execution happens on the server, reducing round-trips and benefitting from cached execution plans. Actual performance depends on workload and design.