SQL Stored Procedures: Syntax, Parameters and Examples

Say several applications need "the orders of one customer, newest first". Each could carry its own copy of the query, or the database can hold it once as a procedure that takes the customer id as a parameter:

-- MySQL
DELIMITER //
CREATE PROCEDURE get_customer_orders(IN p_user_id INT)
BEGIN
  SELECT id, order_date, status, total
  FROM orders
  WHERE user_id = p_user_id
  ORDER BY order_date DESC;
END //
DELIMITER ;

CALL get_customer_orders(77);

Calling the procedure runs its query with 77 in place of p_user_id. You can see exactly what it returns by running the same SELECT with the value filled in:

SELECT id, order_date, status, total
FROM orders
WHERE user_id = 77
ORDER BY order_date DESC;

▶ Run this query in the SQL Playground

-- SQL Server
CREATE PROCEDURE get_customer_orders
    @user_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, order_date, status, total
    FROM orders
    WHERE user_id = @user_id
    ORDER BY order_date DESC;
END;

EXEC get_customer_orders @user_id = 77;

-- PostgreSQL 11+: procedures perform actions; a function is used to return rows
CREATE FUNCTION get_customer_orders(p_user_id INT)
RETURNS TABLE (id INT, order_date DATE, status TEXT, total NUMERIC)
LANGUAGE sql AS $$
  SELECT id, order_date, status, total
  FROM orders
  WHERE user_id = p_user_id
  ORDER BY order_date DESC;
$$;

SELECT * FROM get_customer_orders(77);

MySQL needs DELIMITER in command-line tools so that the semicolons inside the body do not end the CREATE statement early.

Input parameters pass values in; output parameters hand values back. This procedure returns a department's headcount and average salary through two output parameters:

-- MySQL
CREATE PROCEDURE department_stats(
  IN  p_department VARCHAR(50),
  OUT p_headcount  INT,
  OUT p_avg_salary DECIMAL(10,2)
)
BEGIN
  SELECT COUNT(*), AVG(salary)
  INTO p_headcount, p_avg_salary
  FROM employees
  WHERE department = p_department;
END;

CALL department_stats('Engineering', @n, @avg);
SELECT @n, @avg;

SELECT COUNT(*) AS headcount,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE department = 'Engineering';

▶ Run this query in the SQL Playground

Parameters are also the main defence against SQL injection: the value is always treated as data, never as part of the SQL text.

Procedures can declare variables and branch. This one gives a raise only within a limit, and reports what it did:

-- MySQL
CREATE PROCEDURE give_raise(IN p_employee_id INT, IN p_pct DECIMAL(5,2))
BEGIN
  DECLARE v_salary DECIMAL(12,2);

  SELECT salary INTO v_salary FROM employees WHERE id = p_employee_id;

  IF v_salary IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'employee not found';
  ELSEIF p_pct > 10 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'raise above 10% needs approval';
  ELSE
    UPDATE employees SET salary = ROUND(salary * (1 + p_pct / 100))
    WHERE id = p_employee_id;
  END IF;
END;

Before calling it for real, you can preview the effect with a SELECT, here for a 5% raise for employee 4:

SELECT id, first_name, last_name, salary AS current_salary,
       ROUND(salary * 1.05) AS salary_after_raise
FROM employees
WHERE id = 4;

▶ Run this query in the SQL Playground

Procedures can also loop (WHILE, LOOP, cursors), but a single set-based UPDATE or INSERT ... SELECT is almost always faster than processing rows one at a time.

A procedure that changes several tables should do it inside a transaction and undo everything if any step fails:

-- SQL Server
CREATE PROCEDURE place_order
    @user_id INT, @product_id INT, @quantity INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @price DECIMAL(10,2) =
            (SELECT price FROM products WHERE id = @product_id);
        DECLARE @order_id INT;

        INSERT INTO orders (user_id, total, status, order_date)
        VALUES (@user_id, @price * @quantity, 'pending', CAST(GETDATE() AS date));
        SET @order_id = SCOPE_IDENTITY();

        INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal)
        VALUES (@order_id, @product_id, @quantity, @price, @price * @quantity);

        UPDATE products SET stock = stock - @quantity WHERE id = @product_id;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;   -- pass the error on to the caller
    END CATCH;
END;

Without the transaction, a failure after the first INSERT would leave an order with no items. Without THROW (or RESIGNAL in MySQL), the caller would never learn that anything went wrong.

Dynamic SQL builds the statement text at run time, for example to sort by a column the caller chooses. Concatenating caller input into SQL is how injection happens:

-- SQL Server: unsafe
DECLARE @sql NVARCHAR(400) =
  N'SELECT * FROM products WHERE category = ''' + @category + N'''';
EXEC (@sql);   -- @category = ''' OR 1=1 --'' returns every row

-- safe: the value stays a parameter
EXEC sp_executesql
  N'SELECT * FROM products WHERE category = @cat',
  N'@cat NVARCHAR(50)', @cat = @category;

-- column names cannot be parameters: check them against a fixed list
IF @sort_column NOT IN ('name', 'price', 'rating')
    THROW 50001, 'invalid sort column', 1;

Rule of thumb: values go in as parameters; identifiers such as column names are checked against an allow-list before they are put into the text.

A view is a saved SELECT without parameters; a trigger runs automatically on a data change; a CTE lasts for one statement only.

Five errors and how to avoid them.

SET @sql = 'SELECT * FROM users WHERE email = ''' + @email + '''';
EXEC (@sql);

EXEC sp_executesql
  N'SELECT * FROM users WHERE email = @e',
  N'@e NVARCHAR(200)', @e = @email;

INSERT INTO orders ...;
INSERT INTO order_items ...;   -- fails
UPDATE products ...;

BEGIN TRY BEGIN TRANSACTION;
  INSERT ...; INSERT ...; UPDATE ...;
COMMIT; END TRY
BEGIN CATCH ROLLBACK; THROW; END CATCH;

BEGIN CATCH
  ROLLBACK;
END CATCH;

BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK;
  THROW;
END CATCH;

CREATE PROCEDURE remove_inactive AS
DELETE FROM users;

CREATE PROCEDURE remove_user @id INT AS
DELETE FROM users WHERE id = @id;

-- cursor over employees, UPDATE one row per loop

UPDATE employees
SET salary = ROUND(salary * 1.03)
WHERE department = 'Engineering';

4 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy A procedure order_count_by_status(p_status) returns the number of orders with a status. Write its query for the status "shipped". Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS orders
   FROM orders
   WHERE status = 'shipped';

   ▶ Run this query in the SQL Playground

   Inside the procedure, 'shipped' would be the parameter p_status.

2. 2Medium Write the query for a procedure monthly_revenue(p_year) for the year 2024: revenue per month, excluding cancelled and refunded orders. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT strftime('%m', order_date) AS month,
          ROUND(SUM(total), 2) AS revenue
   FROM orders
   WHERE strftime('%Y', order_date) = '2024'
     AND status NOT IN ('cancelled', 'refunded')
   GROUP BY month
   ORDER BY month;

   ▶ Run this query in the SQL Playground

   A reporting procedure keeps this rule in one place for every dashboard.

3. 3Medium A procedure low_stock(p_threshold) lists products below a stock level. Write its query for a threshold of 50. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT id, name, stock
   FROM products
   WHERE stock < 50
   ORDER BY stock;

   ▶ Run this query in the SQL Playground

   The threshold is a parameter, so the same procedure serves every warehouse rule.

4. 4Medium Preview a procedure that would give every Design employee a 3% raise: show current and new salary without changing anything. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name, salary,
          ROUND(salary * 1.03) AS new_salary
   FROM employees
   WHERE department = 'Design'
   ORDER BY salary DESC;

   ▶ Run this query in the SQL Playground

   Running the SELECT version first is a cheap check before an UPDATE in a procedure changes real data.