Stored Procedures & Functions in SQL

The Database Programming Engine While passing raw SQL queries from an application backend over a network connection is the standard way to interact with a database, high-performance systems frequently need to execute complex, multi-step business logic directly on the database server. This server-side logic is handled using Stored Procedures and User-Defined Functions (UDFs) . These are pre-compiled blocks of SQL and procedural code saved directly inside the database catalog. Running code directly on the database server drastically cuts down network latency overhead and allows you to enforce strict, standardized data operations across all your engineering microservices. Stored Procedures vs. User-Defined Functions Before writing server-side code, you must choose the right programming container for your architectural goals. SERVER-SIDE PROGRAMMING BLOCKS │ ┌──────────────────────────┴──────────────────────────┐ ▼ ▼ Stored Procedures User-Defined Functions • Built to execute business actions. • Built to compute & return values. • Can run full transaction controls. • Cannot commit or rollback transactions. • Can run write operations (INSERT/UPDATE). • Read-only context (Strictly mathematical/pure). • Invoked using the `CALL` or `EXEC` command. • Injected directly inside standard `SELECT` queries. 1. Executing Structural Workloads: Stored Procedures A Stored Procedure is designed to group complex operational workflows together. Because procedures can execute write actions and control active transactions, they act as secure, encapsulated API endpoints directly inside your database layer. Production Procedure Blueprint (PostgreSQL Syntax) SQL CREATE OR REPLACE PROCEDURE migrate_node_capacity( IN source_id BIGINT, IN dest_id BIGINT, IN transfer_weight INT, OUT final_source_weight INT, OUT final_dest_weight INT ) LANGUAGE plpgsql AS $ BEGIN -- 1. Deduct capacity weight from the source node UPDATE cluster_nodes SET traffic_weight = traffic_weight - transfer_weight WHERE node_id = source_id; -- 2. Add capacity weight to the destination node UPDATE cluster_nodes SET traffic_weight = traffic_weight + transfer_weight WHERE node_id = dest_id; -- 3. Populate our OUT parameters to return the new states to the caller SELECT traffic_weight INTO final_source_weight FROM cluster_nodes WHERE node_id = source_id; SELECT traffic_weight INTO final_dest_weight FROM cluster_nodes WHERE node_id = dest_id; -- Stored Procedures can manage active transactional states natively COMMIT; END; $; Understanding Parameter Vectors IN Parameters: Input variables passed from your application into the procedure container. These are read-only variables used to guide the internal query logic. OUT Parameters: Output variables populated by the internal script execution. These act as return blocks passed back to the application or calling environment when the execution loop finishes. INOUT Parameters: A dual-purpose variable that enters the procedure holding an initial input value, gets manipulated by the code, and exits holding a brand-new updated value. Calling a Saved Stored Procedure To trigger a compiled procedure from a backend application runner or terminal session, use the CALL command (or EXECUTE / EXEC in Microsoft SQL Server setups): SQL -- Invoking our migration routine with explicit input variables CALL migrate_node_capacity(10, 25, 150, null, null); 2. Computing Clean Values: User-Defined Functions (UDFs) A User-Defined Function is built strictly to perform computations and return either a single scalar value or a structured data table. To ensure stability, functions are strictly prohibited from modifying database data or controlling active transactions. They must act as Pure Functions : given the same input variables, a function should return the exact same output value without creating any side effects on disk storage. Production Function Blueprint (Scalar Computation) SQL CREATE OR REPLACE FUNCTION calculate_efficiency_ratio( traffic_weight INT, active_connections INT ) RETURNS NUMERIC LANGUAGE plpgsql AS $ DECLARE computed_ratio NUMERIC; BEGIN -- Defensive check to handle division by zero smoothly IF active_connections = 0 THEN RETURN 0.00; END IF; -- Compute our business metric computed_ratio := (traffic_weight::NUMERIC / active_connections::NUMERIC) * 100; RETURN ROUND(computed_ratio, 2); END; $; Calling a User-Defined Function Because functions do not use transaction locks and simply return values, you do not call them using the CALL statement. Instead, you inject them straight into standard SQL projection blocks, using them exactly like a built-in mathematical function: SQL -- Evaluating live network telemetry metrics using our custom function wrapper SELECT node_name, traffic_weight, current_connections, calculate_efficiency_ratio(traffic_weight, current_connections) AS node_efficiency_index FROM cluster_nodes WHERE is_active = TRUE; Stored Procedures & Functions Reference Matrix Programming Vector Core Supported Parameter Modes Transactional Capabilities Primary Production Application Role Stored Procedure IN , OUT , INOUT variables. Complete Control. Natively supports COMMIT and ROLLBACK commands. Encapsulating complex backend write tasks, dynamic multi-table batch jobs, and security-hardened write routines. User-Defined Function Primarily IN inputs (Can return explicit data tables). Strictly Forbidden. Cannot modify data or control active transactions. Automating formatting rules, computing complex mathematical or financial ratios, and handling repetitive data parsers directly within queries.