¿Cómo crear y utilizar correctamente subprogramas (Procedures/Functions) en SQL para implementar lógica de negocio reutilizable, y cuáles son los detalles sobre la gestión de parámetros y valores de retorno?

Respuesta.

Los subprogramas en SQL se dividen en dos tipos:

• Procedimientos (Stored Procedures): realizan acciones (inserción, actualización, eliminación), pueden devolver parámetros OUT o conjuntos de filas (a través de SELECT). Se llaman mediante CALL/EXEC y no siempre se utilizan en la capa select.
• Funciones (User Defined Functions, UDF): devuelven un valor (escalar o tabular), pueden ser utilizadas directamente en SELECT, WHERE, ORDER BY y otras construcciones.

Importante:

• Los procedimientos NO pueden ser utilizados en un SELECT normal, solo a través de llamadas con EXEC/CALL.
• Las funciones solo pueden devolver un valor (escalar) o una tabla (funciones de tabla), y pueden ser utilizadas en cualquier expresión.
• Siempre documenta los parámetros: IN (para entrada), OUT (para devolver el resultado), INOUT (bidireccional).

Ejemplo de función escalar (PostgreSQL):

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

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

Ejemplo de procedimiento almacenado (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;

Pregunta engañosa.

¿Se puede usar un procedimiento almacenado directamente en SELECT?

A menudo se responde "sí", pero esto es incorrecto.

Respuesta:

• No se puede: los procedimientos estándar se llaman solamente por separado (EXEC/CALL), pero las funciones sí pueden ser utilizadas en SELECT.

---

Ejemplos de errores reales por desconocer los detalles del tema.

---

Historia

Proyecto: Sistema de contabilidad primaria, implementación de informes. Error: En lugar de una función, se escribió un procedimiento para calcular la suma — SELECT no funcionó, fue necesario reescribir toda la lógica de los informes de los usuarios.

---

Historia

Proyecto: Sistema ERP con parámetros externos. Error: No se definió la clave OUT para el procedimiento, como resultado, el cliente no pudo conocer el ID del registro añadido, la integración se "rompió".

---

Historia

Proyecto: Servicio financiero con cálculos de impuestos según diferentes reglas. Error: Se utilizó una función escalar en una consulta masiva sin prueba de rendimiento — la consulta ralentizó el procesamiento de la tabla debido a la llamada fila por fila (plan no optimizado).