Procedures and UDFs

While SQL is a declarative language, sometimes you need to perform complex, multi-step operations or reuse specific logic across many queries. This is where Stored Procedures and User Defined Functions (UDFs) come in.

Definition: Procedures vs. Functions

Feature	Stored Procedure	User Defined Function (UDF)
Return Value	Can return multiple values or none.	Must return exactly one value (or a table).
Usage	Called using `CALL` or `EXEC`.	Called directly in a `SELECT` or `WHERE`.
Side Effects	Can modify data (INSERT/UPDATE).	Usually read-only (in most dialects).
Transactions	Can control transactions (`COMMIT`).	Cannot control transactions.

Context: When to Use Stored Logic

Security (The “API” Pattern): Instead of giving a user access to the Salaries table, you give them access to a procedure GiveRaise(emp_id, amount). They can’t see other data; they can only run your approved logic.
Complexity: If a business process requires 10 different SQL steps, putting them in a procedure ensures they are always run in the correct order.
Performance: Procedures are pre-compiled and stored on the server, which can slightly reduce the overhead of sending long SQL strings over the network.

Detailed Explanation: Syntax (Postgres)

1. Creating a Function

CREATE OR REPLACE FUNCTION GetTax(price DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  RETURN price * 0.08;
END;
$$ LANGUAGE plpgsql;

2. Creating a Procedure

CREATE OR REPLACE PROCEDURE ProcessOrder(order_id INT) AS $$
BEGIN
  UPDATE Inventory SET stock = stock - 1 WHERE prod_id = order_id;
  UPDATE Orders SET status = 'Processed' WHERE id = order_id;
  COMMIT;
END;
$$ LANGUAGE plpgsql;

Example: Calling the Logic

-- Using the function in a query
SELECT Name, Price, GetTax(Price) FROM Products;

-- Calling the procedure
CALL ProcessOrder(101);

Under the Hood: The Procedural Language

When you write a procedure, you aren’t using standard SQL; you are using a Procedural Language extension:

PL/pgSQL: PostgreSQL
T-SQL: SQL Server
PL/SQL: Oracle

These languages add features like:

Variables: DECLARE my_val INT;
Loops: WHILE or FOR loops.
Conditional Logic: IF / THEN / ELSE.
Exception Handling: TRY / CATCH.

The “UDF” Performance Trap

Be careful using Scalar UDFs in the SELECT list. In many databases, the engine cannot optimize a UDF. It must “stop” the set-based processing and run the function’s procedural code for every single row. If you have 1 million rows, the function runs 1 million times, which can be catastrophically slow.