SQL Stored Procedures with Parameters

SQL Stored Procedures with Parameters

A stored procedure is a precompiled collection of SQL statements stored in the database. Procedures encapsulate complex logic, improve performance through precompilation, and provide a controlled interface to the data.

PostgreSQL Syntax

CREATE OR REPLACE PROCEDURE transfer_funds(
  IN sender_id INTEGER,
  IN receiver_id INTEGER,
  IN amount DECIMAL(10,2)
)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
  COMMIT;
END;
$$;

CALL transfer_funds(1, 2, 500.00);

MySQL Syntax

DELIMITER //
CREATE PROCEDURE get_employee_by_dept(
  IN dept_name VARCHAR(50),
  OUT employee_count INT
)
BEGIN
  SELECT COUNT(*) INTO employee_count
  FROM employees
  WHERE department = dept_name;
END //
DELIMITER ;

CALL get_employee_by_dept('Engineering', @count);
SELECT @count;

Error Handling

CREATE OR REPLACE PROCEDURE safe_transfer(
  IN sender_id INT, IN receiver_id INT, IN amount DECIMAL
)
LANGUAGE plpgsql AS $$
DECLARE
  sender_balance DECIMAL;
BEGIN
  SELECT balance INTO sender_balance
  FROM accounts WHERE id = sender_id FOR UPDATE;

  IF sender_balance < amount THEN
    RAISE EXCEPTION 'Insufficient funds: balance is %', sender_balance;
  END IF;

  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
END;
$$;

Procedures vs Functions

Feature	Procedure	Function
Return value	Via OUT params	Returns a value
Use in SELECT	No	Yes
Transaction control	Can COMMIT/ROLLBACK	Cannot (usually)
Invocation	CALL	SELECT or expression

Best Practices

• Use stored procedures for operations requiring transaction control or multiple steps
• Keep procedures focused on a single responsibility
• Always include error handling and input validation
• Document parameters and expected behavior
• Use functions instead when you need a return value in a query
• Version control your procedures alongside application code

When to Use

• Complex business logic that must be consistent across multiple applications
• Operations requiring transaction management
• Batch processing and data transformations
• Security: grant EXECUTE permission without direct table access

Stored procedures are a powerful tool for encapsulating business logic, but should be balanced with application-level code for maintainability.