Views & Stored Procedures

Reviewed & published by Brayan K

By the end of this lesson you'll be able to package SQL for reuse: views let you save a query under a name and treat it like a table, and stored procedures let you bundle logic the database can run on command. These are the tools that turn a pile of queries into a clean, shareable database.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Create a view with CREATE VIEW and query it like a table
• Use views to reuse logic, simplify queries, and hide columns
• Tell updatable views from read-only ones
• Write a stored procedure with parameters using CREATE PROCEDURE
• Run a procedure with CALL and read back an OUT value
• Recognise that procedure syntax varies by database engine

Our Sample Table: products

Every example in this lesson runs against this familiar products table. The views and procedures you build all read from it, so keep its rows in mind.

1. What Is a View?

A view is a saved, named query. You write a SELECT once, give it a name, and from then on you can SELECT from that name as if it were a real table. A view stores no data of its own — it simply re-runs its saved query every time you use it, so it's always in sync with the underlying tables.

A view is a saved search. Think of "Recently added, under $20" saved on a shopping site: you don't keep a copy of those products — the site re-runs the search each time you open it. A view is that saved search, living inside your database.

You create one with CREATE VIEW name AS followed by any SELECT. Here we save the "Electronics only" query as a view called electronics:

-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL,
    stock        INTEGER
);

INSERT INTO products (id, product_name, category, price, stock) VALUES
    (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
    (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
    (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
    (4, 'Notebook',            'Stationery',   3.25, 500),
    (5, 'Desk Lamp',           'Home',        32.00,  80),
    (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- A VIEW is a saved, named query. It stores NO data of its own —
-- it just remembers the SELECT and re-runs it every time you use it.
CREATE VIEW electronics AS
SELECT product_name, price, stock
FROM products
WHERE category = 'Electronics';

-- Now query the view exactly like a table:
SELECT * FROM electronics;

-- Behind the scenes the database runs the saved SELECT again,
-- so the view is ALWAYS up to date with the products table.

-- ✅ Expected result:
-- product_name | price | stock
-- Wireless Mouse | 24.99 | 120
-- Mechanical Keyboard | 79 | 45
-- USB-C Cable | 12.99 | 200

Notice the view returns only the three Electronics rows and only the columns you selected. The WHERE category = 'Electronics' rule now lives inside the view, so you never have to type it again.

2. Why Views Are Useful

Views earn their keep in three ways. A view behaves like a table, so you can filter and sort it further — reusing the logic baked inside it:

-- Because a view behaves like a table, you can filter,
-- sort, and pick columns from it — no need to repeat the WHERE.
SELECT product_name, price
FROM electronics          -- the view we created above
WHERE price > 20
ORDER BY price DESC;

-- You wrote the "category = 'Electronics'" rule ONCE, in the view.
-- Every query against the view reuses it automatically.

-- ✅ Expected result:
-- product_name | price
-- Mechanical Keyboard | 79
-- Wireless Mouse | 24.99

Views also simplify nightmare queries (hide a 10-table JOIN behind one friendly name) and provide security — you can grant access to a view that exposes only safe columns while the sensitive ones stay hidden:

-- A view can hide columns you don't want everyone to see.
-- Imagine a 'staff' table with a salary column — this view
-- exposes only the safe columns and keeps salary private.
CREATE VIEW staff_public AS
SELECT staff_id, first_name, department
FROM staff;
-- salary is simply not selected, so it can never leak through staff_public.

-- A view can also rename and combine columns for a clean report:
CREATE VIEW price_list AS
SELECT
    product_name AS item,
    price        AS "Price (USD)"
FROM products;

SELECT * FROM price_list;

-- ✅ Expected result:
-- item | Price (USD)
-- Wireless Mouse | 24.99
-- Coffee Mug | 9.5
-- Mechanical Keyboard | 79
-- Notebook | 3.25
-- Desk Lamp | 32
-- USB-C Cable | 12.99

Your Turn: build a view

Fill in the two blanks to create an electronics view and query it. The expected result is in the comments so you can check yourself.

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: create a view of just the Electronics products, then query it.

CREATE VIEW ___ AS               -- 👉 name the view: electronics
SELECT product_name, price, stock
FROM products
WHERE category = '___';          -- 👉 the category to keep: Electronics

SELECT * FROM electronics;       -- query the view like a table

-- ✅ Expected result: 3 rows (Wireless Mouse, Mechanical Keyboard,
--    USB-C Cable) with columns product_name, price, stock

3. Updatable vs. Read-Only Views

Most views are used for reading, but a simple view — one table, no GROUP BY, DISTINCT, or aggregate functions — is often updatable: an INSERT, UPDATE, or DELETE on the view flows straight through to the real table. As soon as a view summarises or combines rows it becomes read-only, because the database can no longer tell which underlying row your change should affect.

Rule of thumb: if you could point at one exact source row for every row the view shows, it can usually be updated. If the view groups, counts, or joins many rows into one, treat it as read-only.

-- SIMPLE views (one table, no GROUP BY / DISTINCT / aggregate)
-- are often UPDATABLE — writing to them writes to the real table:
UPDATE electronics
SET price = price * 0.9     -- 10% off all electronics
WHERE product_name = 'Wireless Mouse';
-- This actually changes the products table, because 'electronics'
-- maps row-for-row back to it.

-- COMPLEX views are READ-ONLY. This view groups rows, so the
-- database can't tell which real row an UPDATE should touch:
CREATE VIEW category_counts AS
SELECT category, COUNT(*) AS items
FROM products
GROUP BY category;
-- SELECT works fine; UPDATE/INSERT/DELETE on it will be rejected.

4. Updating & Dropping a View

Change a view's definition with CREATE OR REPLACE VIEW, and remove it with DROP VIEW. Because a view holds no data, dropping it never touches the table it reads from.

-- Replace a view's definition without dropping it first:
CREATE OR REPLACE VIEW electronics AS
SELECT product_name, price          -- dropped the 'stock' column
FROM products
WHERE category = 'Electronics';

-- Remove a view entirely (the underlying table is untouched):
DROP VIEW electronics;

-- 'IF EXISTS' avoids an error if the view was already gone:
DROP VIEW IF EXISTS electronics;

5. Stored Procedures

A stored procedure is a named block of SQL saved in the database that you run on demand with CALL. Unlike a view (which is always a single SELECT), a procedure can take parameters, run several statements, and contain logic. You write it once; anyone can run it by name without knowing what's inside.

If a view is a saved search, a procedure is a reusable recipe. You hand it ingredients (the parameters — say, a category name), it follows fixed steps, and gives you the finished dish. The same recipe, different inputs, every time.

This MySQL procedure takes one input parameter and returns the matching products. IN marks a value you pass in; CALL runs it:

-- A stored procedure is a reusable, named block of SQL you run by name.
-- This MySQL example takes one input parameter (a category) and
-- returns the matching products. CALL runs it.
DELIMITER //                         -- let the body use ; safely (MySQL only)
CREATE PROCEDURE products_in(
    IN wanted_category VARCHAR(50)   -- IN = a value you pass in
)
BEGIN
    SELECT product_name, price
    FROM products
    WHERE category = wanted_category
    ORDER BY price;
END //
DELIMITER ;                          -- put the delimiter back to ;

-- Run it, passing 'Electronics' as the argument:
CALL products_in('Electronics');

Procedures can also return a value through an OUT parameter. Here the procedure counts the products in a category and writes that number into how_many, which you read back afterwards:

-- Procedures can also hand a value back through an OUT parameter.
DELIMITER //
CREATE PROCEDURE count_in(
    IN wanted_category VARCHAR(50),  -- value you pass in
    OUT how_many INT                 -- value the procedure fills in for you
)
BEGIN
    SELECT COUNT(*) INTO how_many    -- store the count in the OUT parameter
    FROM products
    WHERE category = wanted_category;
END //
DELIMITER ;

CALL count_in('Electronics', @result);  -- @result captures the OUT value
SELECT @result;                          -- read it back: 3

Your Turn: finish the procedure

Complete the procedure body so cheaper_than returns every product below the price you pass in. Two blanks — the table and the comparison.

-- 🎯 YOUR TURN — complete this stored procedure (MySQL style).
-- Goal: a procedure that returns every product cheaper than a price you pass in.

DELIMITER //
CREATE PROCEDURE cheaper_than(
    IN max_price DECIMAL(10,2)    -- the price ceiling you pass in
)
BEGIN
    SELECT product_name, price
    FROM ___                      -- 👉 which table? products
    WHERE price < ___;            -- 👉 compare against the parameter: max_price
END //
DELIMITER ;

CALL cheaper_than(15.00);

-- ✅ Expected result: 3 rows priced under 15.00
--    (Coffee Mug 9.50, Notebook 3.25, USB-C Cable 12.99)

6. Syntax Varies by Engine

Views are written almost identically everywhere, but procedure syntax differs by database engine. MySQL uses DELIMITER and BEGIN ... END; PostgreSQL wraps the body in a $$ ... $$ block with a LANGUAGE; SQL Server uses AS and runs procedures with EXEC. The idea — a named, parameterised block you call by name — is the same; only the wrapping changes.

-- IMPORTANT: procedure syntax differs by database engine.

-- PostgreSQL — no DELIMITER; the body lives inside a $$ ... $$ block:
CREATE PROCEDURE products_in(wanted_category TEXT)
LANGUAGE SQL
AS $$
    SELECT product_name, price FROM products WHERE category = wanted_category;
$$;
CALL products_in('Electronics');

-- SQL Server — uses AS (no BEGIN required) and EXEC to run it:
CREATE PROCEDURE products_in @wanted_category VARCHAR(50)
AS
    SELECT product_name, price FROM products WHERE category = @wanted_category;
EXEC products_in @wanted_category = 'Electronics';

Common Errors (and the fix)

• Thinking a view stores data: it doesn't. A view re-runs its SELECT every time you query it, so it's always current — but querying a huge view can be slow because the work happens each time. It is a saved query, not a saved copy.
• "table or view already exists": you ran CREATE VIEW twice. Use CREATE OR REPLACE VIEW to redefine, or DROP VIEW IF EXISTS first.
• Procedure syntax error on the wrong engine: a MySQL DELIMITER // ... END // block fails on PostgreSQL/SQL Server. Match the syntax to your database (see section 6).
• Dropping a table a view depends on: DROP TABLE products while a view reads from it leaves the view broken — queries against it then error with "no such table". Drop or update the dependent views too.
• Trying to UPDATE a grouped view: "target view is not updatable". Views with GROUP BY, DISTINCT, or aggregates are read-only — modify the base table instead.

Frequently Asked Questions

Q: Does a view make my queries faster?

Not by itself — a standard view re-runs its query each time, so it's about the same speed as writing that query out. It saves your effort, not the database's. (A separate feature, the materialized view, does cache results, but it can go stale.)

Q: What's the difference between a view and a stored procedure?

A view is always a single SELECT you query like a table. A procedure is a block of one or more statements with parameters that you CALL to perform an action. Use a view to reshape data for reading; use a procedure to run logic.

Q: Why won't my procedure run — it worked in another tool?

Almost certainly an engine mismatch. MySQL, PostgreSQL, and SQL Server each have different procedure syntax (DELIMITER vs $$ vs AS / EXEC). Make sure your playground is set to the same engine the example was written for.

Q: If I drop a view, do I lose data?

No. A view holds no data of its own, so DROP VIEW only removes the saved query. The tables it read from are completely unaffected.

Mini-Challenge: Low-Stock Report

Put it together — a brief, a blank canvas, and the expected result in the comments. Write the view and the query, then copy them into a playground to confirm.

-- 🎯 MINI-CHALLENGE: a "low stock" report view
-- Using ONLY what this lesson covered (CREATE VIEW, SELECT, WHERE):
--   1. Create a view called low_stock
--   2. It should show product_name, category and stock
--      for every product with stock below 100
--   3. Then query the view, sorted by stock (lowest first)
--
-- ✅ Expected: 2 rows — Mechanical Keyboard (45) and Desk Lamp (80)
--    with columns product_name, category, stock

-- your CREATE VIEW + SELECT here

🎉 Lesson Complete

• ✅ A view is a saved query you CREATE VIEW once and then SELECT from like a table
• ✅ Views give you reuse, simpler queries, and security by hiding columns
• ✅ Simple views are updatable; grouped/aggregated views are read-only
• ✅ A stored procedure bundles parameterised logic you run with CALL
• ✅ Procedure syntax differs across MySQL, PostgreSQL, and SQL Server
• ✅ Next: Transactions & ACID — grouping changes so they all succeed or all fail together

Practice quiz

Continue this course

• Previous: Indexes & Performance
• Next: Transactions & ACID — Ensure data integrity with transactions, rollbacks, and ACID guarantees
• Quick reference: SQL cheat sheet › Views & Transactions