6.4 Stored Procedures

Level: 6 | Version: v1.1 | Author: Meptrasoft

Overview

A stored procedure is a reusable set of SQL statements stored in the database and executed using a CALL statement.

It can accept parameters and perform multiple operations as one reusable unit.

INPUT PARAMETERS
      ↓
STORED PROCEDURE
      ↓
SQL OPERATIONS
      ↓
RESULT / DATA CHANGES

Stored Procedure → Reusable database logic

PostgreSQL Stored Procedure: Reusable Database Logic executed with CALL — Figure 1: A PostgreSQL stored procedure accepts parameters and executes reusable database logic, including multiple SQL operations, through a single CALL.

PostgreSQL Example

Create a procedure that inserts an employee and their salary:

CREATE OR REPLACE PROCEDURE insert_employee(
    p_emp_name VARCHAR,
    p_dept_id INT,
    p_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_emp_id INT;
BEGIN
    INSERT INTO employees (emp_name, dept_id)
    VALUES (p_emp_name, p_dept_id)
    RETURNING emp_id INTO v_emp_id;

    INSERT INTO salaries (emp_id, salary)
    VALUES (v_emp_id, p_salary);
END;
$$;

Here, the procedure accepts:

p_emp_name → Employee name
p_dept_id  → Department ID
p_salary   → Salary

It then performs two related inserts.

Calling the Procedure

Use CALL to execute the procedure:

CALL insert_employee('Rahul', 2, 65000);

This executes the stored logic and adds the employee and salary.

CALL → Execute the stored procedure

Why Use Stored Procedures?

Stored procedures are useful when the same database logic needs to be executed repeatedly.

They can:

Accept parameters
Perform multiple SQL operations
Reuse business logic
Reduce repeated SQL in applications
Centralize database-side operations

Procedure vs Function

A procedure and function are related but not identical.

Feature	Procedure	Function
Invocation	Executed with `CALL`	Commonly used in expressions such as `SELECT`
Operations	Can perform procedural/database operations	Returns a value or result
Best Used For	Multi-step operations and workflows	Reusable calculations, formatting, or scalar logic

Note: Exact capabilities vary by database system.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
A procedure is the same as a table	A procedure stores reusable database logic
A procedure is called with `SELECT`	PostgreSQL procedures are invoked with `CALL`
A procedure can never accept parameters	Procedures can accept parameters
A procedure must contain only one SQL statement	It can contain multiple statements
Stored procedures are identical across all databases	Syntax and capabilities vary by database system

Placement Quick Points

PROCEDURE
→ REUSABLE DATABASE LOGIC

PARAMETERS
→ PROVIDE INPUT

CALL
→ EXECUTE PROCEDURE

PL/pgSQL
→ POSTGRESQL PROCEDURAL LANGUAGE

A stored procedure is reusable logic stored in the database.
PostgreSQL procedures are created using CREATE PROCEDURE.
PostgreSQL procedures are executed using CALL.
Procedures can accept parameters and perform multiple operations.
Procedure syntax and behavior vary between database systems.

Interview Questions

What is a stored procedure?

A stored procedure is reusable database-side logic stored in the database and executed as a unit.

How do you execute a PostgreSQL procedure?

Using CALL.

CALL insert_employee('Rahul', 2, 65000);

Can a stored procedure accept parameters?

Yes. Parameters allow the same procedure to be used with different input values.

Why are stored procedures useful?

They allow reusable database logic and can perform multiple related operations from a single procedure call.

What is the difference between a procedure and a function?

In PostgreSQL, a procedure is invoked with CALL, while a function returns a value/result and is commonly invoked in expressions such as SELECT.

Practice & Hands-On Exercises

Using the PostgreSQL employees and salaries tables:

Create the insert_employee procedure:

CREATE OR REPLACE PROCEDURE insert_employee(
    p_emp_name VARCHAR,
    p_dept_id INT,
    p_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_emp_id INT;
BEGIN
    INSERT INTO employees (emp_name, dept_id)
    VALUES (p_emp_name, p_dept_id)
    RETURNING emp_id INTO v_emp_id;

    INSERT INTO salaries (emp_id, salary)
    VALUES (v_emp_id, p_salary);
END;
$$;

Call it with a new employee:

CALL insert_employee('Rahul', 2, 65000);

Verify that the employee was inserted:

SELECT * FROM employees WHERE emp_name = 'Rahul';

Verify that the salary was inserted:

SELECT * FROM salaries WHERE emp_id = (SELECT emp_id FROM employees WHERE emp_name = 'Rahul');

Explain the difference between a stored procedure and a function.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

CREATE PROCEDURE
       ↓
ACCEPT PARAMETERS
       ↓
EXECUTE SQL LOGIC
       ↓
CALL PROCEDURE

A PostgreSQL stored procedure is reusable database logic that can accept parameters and execute multiple SQL operations through CALL.

End of Stored Procedures