What is a Stored Procedure?

A Stored Procedure is a named collection of SQL statements stored inside a database and executed when needed. Stored procedures are commonly used to group repetitive database operations into a reusable unit.

Stored procedures are supported by database systems such as SQL Server, MySQL, PostgreSQL, and Oracle, although the syntax and capabilities differ between database systems.

Important Note About SQLite

SQLite does not support traditional stored procedures. Because your online SQL editor runs SQLite, the examples below demonstrate the same concepts using regular SQLite statements so that every Test My Code example remains executable.

Basic Stored Procedure Concept

In database systems that support stored procedures, a procedure can contain multiple SQL statements and can then be called by its name.

-- Conceptual example for databases
-- that support stored procedures:

CREATE PROCEDURE GetProducts
AS
BEGIN
    SELECT product_name, price
    FROM products;
END;

Note: The code above is a conceptual stored-procedure example and is intentionally not marked as SQLite-executable.

Reusable SQL Logic in SQLite

Since SQLite has no stored procedure feature, reusable database logic can often be represented with views, application functions, or prepared statements.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_name, category, price)
VALUES
  ('Wireless Keyboard', 'Electronics', 2499.00),
  ('Desk Lamp', 'Office', 1299.50),
  ('USB Hub', 'Electronics', 799.00),
  ('Notebook', 'Stationery', 199.00);

-- Reusable query logic:
SELECT product_name, category, price
FROM products
WHERE price > 1000
ORDER BY price DESC;

Stored Procedure with Multiple Statements

A stored procedure can contain multiple operations. For example, a procedure could insert a new order and then retrieve the order details. SQLite can execute these statements separately, even though it cannot package them as a stored procedure.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO orders (order_id, customer_name, amount)
VALUES (1, 'Aarav', 3499.00);

INSERT INTO orders (order_id, customer_name, amount)
VALUES (2, 'Meera', 2199.50);

-- Retrieve the data after the inserts:
SELECT *
FROM orders
ORDER BY order_id;

Stored Procedure with Parameters

Stored procedures can accept parameters in database systems that support them. Parameters allow the same procedure to work with different input values.

-- Conceptual syntax:
-- CREATE PROCEDURE FindProducts
--     @category TEXT
-- AS
-- BEGIN
--     SELECT product_name, price
--     FROM products
--     WHERE category = @category;
-- END;

-- SQLite-compatible equivalent:
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_name, category, price)
VALUES
  ('Mechanical Keyboard', 'Computer', 3499),
  ('Office Chair', 'Furniture', 7499),
  ('Wireless Mouse', 'Computer', 1299);

SELECT product_name, price
FROM products
WHERE category = 'Computer';

Stored Procedure for Calculations

Stored procedures can also perform calculations before returning a result. The following SQLite example demonstrates the calculation that could otherwise be placed inside a stored procedure.

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price REAL NOT NULL
);

INSERT INTO sales (product_name, quantity, unit_price)
VALUES
  ('Laptop Stand', 3, 1800),
  ('USB Cable', 5, 450),
  ('Desk Lamp', 2, 1250);

SELECT
  product_name,
  quantity,
  unit_price,
  quantity * unit_price AS total_amount
FROM sales
ORDER BY total_amount DESC;

Stored Procedure and Transactions

In database systems that support stored procedures, a procedure may perform several operations within a transaction. SQLite also supports transactions, although the transaction itself is not a stored procedure.

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  account_name TEXT NOT NULL,
  balance REAL NOT NULL
);

INSERT INTO accounts (account_id, account_name, balance)
VALUES
  (1, 'North Account', 5000),
  (2, 'South Account', 3000);

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

COMMIT;

SELECT *
FROM accounts
ORDER BY account_id;

Stored Procedure vs SQL Function

A stored procedure and a database function are not always the same. A function generally returns a value and may be used inside SQL expressions, while a procedure is typically designed to perform one or more operations.

Advantages of Stored Procedures

• Encapsulate frequently used database operations.
• Reduce repetition of complex SQL logic.
• Can improve consistency across applications.
• Can provide controlled access to database operations.
• May reduce application-side database code.
• Can support transactions and complex workflows in supported systems.

Limitations of Stored Procedures

• Syntax differs between database systems.
• SQLite does not provide traditional stored procedures.
• Complex procedures can become difficult to maintain.
• Moving procedures between database systems may require rewriting them.
• Business logic placed heavily inside the database can make application architecture more complex.

Stored Procedure Syntax Examples

SQLite Alternative: Views

When you need reusable query logic in SQLite, a VIEW can sometimes be a useful alternative to a stored procedure.

DROP VIEW IF EXISTS expensive_products;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_name, price)
VALUES
  ('Monitor', 12500),
  ('Keyboard', 2500),
  ('Office Chair', 8500),
  ('Laptop Stand', 1800);

CREATE VIEW expensive_products AS
SELECT product_id, product_name, price
FROM products
WHERE price >= 5000;

SELECT *
FROM expensive_products
ORDER BY price DESC;

Best Practices

• Keep stored procedures focused on a clear task.
• Use meaningful procedure names.
• Document parameters and expected results.
• Use transactions when multiple operations must succeed together.
• Validate input parameters in the application and database where appropriate.
• Avoid unnecessary complexity inside stored procedures.
• Remember that stored-procedure syntax is database-specific.