← Back to Knowledge Base← Previous questionNext question →
ProgrammingDatabase Developer / Database Architect

How to correctly create and use subprograms (Procedure/Function) in SQL to implement reusable business logic, and what are the nuances of managing parameters and return values?

Pass interviews with Hintsage AI assistant

Answer.

Subprograms in SQL come in two types:

  • Procedures (Stored Procedures): perform actions (insert, update, delete), can return OUT parameters or result sets (via SELECT). They are called using CALL/EXEC and are not always used in the select layer.
  • Functions (User Defined Functions, UDF): return a value (scalar or table), can be used directly in SELECT, WHERE, ORDER BY, and other constructs.

Important:

  • Procedures CANNOT be used in a regular SELECT, only through a call with EXEC/CALL.
  • Functions can return only one value (scalar) or a table (table-valued functions), and can be used in any expression.
  • Always document parameters: IN (for input), OUT (for returning results), INOUT (bidirectional).

Example of a scalar function (PostgreSQL):

CREATE FUNCTION get_tax(amount NUMERIC, rate NUMERIC DEFAULT 0.13) RETURNS NUMERIC AS $$
BEGIN
  RETURN amount * rate;
END;
$$ LANGUAGE plpgsql;

-- usage:
SELECT *, get_tax(price) AS tax FROM product;

Example of a stored procedure (SQL Server):

CREATE PROCEDURE add_employee(@name NVARCHAR(100), @salary INT, @emp_id INT OUTPUT)
AS
BEGIN
  INSERT INTO employees (name, salary) VALUES (@name, @salary);
  SET @emp_id = SCOPE_IDENTITY();
END;

DECLARE @id INT;
EXEC add_employee 'John', 100000, @id OUTPUT;

Trick question.

Can a stored procedure be used directly in SELECT?

Many answer "yes", but this is incorrect.

Answer:

  • No: standard procedures are called separately (EXEC/CALL), while functions can be used in SELECT.

Examples of real errors due to ignorance of the nuances of the topic.

Story

Project: Primary accounting system, implementation of reports. Error: Instead of a function, a procedure was written to calculate the sum — the SELECT did not work, had to rewrite all the logic of user reports.

Story

Project: ERP system with external parameters. Error: Did not set the OUT key for the procedure, as a result the client could not know the ID of the added record, the integration "broke".

Story

Project: Financial service with tax calculations based on different rules. Error: Used a scalar function in a bulk query without performance testing — the query slowed down table processing due to row-by-row invocation (non-optimized plan).