Views and Stored Procedures: Reusable SQL

Introduction

Views and stored procedures are the two primary mechanisms for creating reusable, encapsulated SQL logic in a Database Management System. Views abstract complex queries behind a simple name, providing consistent, secure access to data. Stored procedures encapsulate business logic in the database layer, reducing network round trips and centralizing complex operations.

Explanation

A view is a named virtual table defined by a SELECT query. It does not store data itself; instead, the query is executed each time the view is accessed. Views are useful for simplifying complex queries, restricting access to sensitive data, and presenting data in a structured format.

A stored procedure is a precompiled SQL program stored in the database. It can accept input parameters, execute multiple SQL statements, include control logic (such as IF/ELSE and loops), and return results. Stored procedures help centralize database operations and reduce repeated query processing.

Real World Example

An HR system uses a view called employee_summary that joins employees, departments, and salaries tables — developers query this view without knowing the underlying schema complexity. A stored procedure called process_payroll runs at month-end: it calculates salaries, applies deductions, inserts payslip records, and updates payment status — all in one database call instead of ten separate application queries.

Technical Breakdown

View types:

• Simple view: Based on one table, no functions, no GROUP BY — usually updatable (INSERT/UPDATE/DELETE work through the view).

• Complex view: Based on multiple tables or using GROUP BY, DISTINCT, aggregate functions — usually read-only.

• Materialized view: (PostgreSQL, Oracle) Actually stores the query result on disk. Must be refreshed manually or on schedule. Faster to query but can be stale.

Example

-- Create a view for order summaries
CREATE VIEW order_summary AS
SELECT
    o.order_id,
    c.name        AS customer_name,
    c.email,
    o.order_date,
    o.status,
    COUNT(oi.product_id)           AS item_count,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
INNER JOIN customers c   ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id   = oi.order_id
GROUP BY o.order_id, c.name, c.email, o.order_date, o.status;

-- Query the view like a table
SELECT * FROM order_summary
WHERE status = 'pending'
ORDER BY order_date DESC;

-- Stored procedure with parameters (MySQL syntax)
DELIMITER $$
CREATE PROCEDURE get_customer_orders(
    IN  p_customer_id INT,
    IN  p_status      VARCHAR(20),
    OUT p_total_count INT
)
BEGIN
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.customer_id = p_customer_id
      AND (p_status IS NULL OR o.status = p_status);

    SELECT COUNT(*) INTO p_total_count
    FROM orders
    WHERE customer_id = p_customer_id;
END$$
DELIMITER ;

-- Call the stored procedure
CALL get_customer_orders(101, 'completed', @count);
SELECT @count;

Common Mistakes

• Querying a complex view inside a loop — each reference re-executes the underlying query; use materialized views or CTEs for performance-critical paths.

• Granting SELECT on sensitive base tables instead of using security views — views allow row/column-level access control without exposing underlying table structure.

• Writing stored procedures with too much business logic — excessive logic in the database makes testing, versioning, and deployment harder; use procedures for database-centric operations, not full application logic.

Interview Tips

Be ready to explain the performance difference between a view and a materialized view: a regular view re-executes its query every time, adding no performance benefit; a materialized view caches the result, making queries faster at the cost of potential staleness. Also explain why stored procedures reduce network round trips — a single CALL executes multiple SQL statements server-side instead of the application making N separate database round trips.