241015 - Procedimientos, funciones Y triggers

Clase

Pasted image 20241015182442.png

Here’s a quick comparison between SQL stored procedures and functions:

Purpose: Perform a series of operations (e.g., inserting, updating, or deleting data). Typically used for executing business logic or a set of statements that don't necessarily return a result.
Return Value: Can return multiple result sets, no value, or status codes.
Parameters: Can have input (IN), output (OUT), and input/output (INOUT) parameters.
Side Effects: Can modify data (e.g., INSERT, UPDATE, DELETE).
Usage: Called with CALL or EXEC statement. Useful for batch processing or encapsulating complex logic.

Purpose: Compute and return a single value based on input parameters. They are typically used for calculations or transformations.
Return Value: Must return a single scalar value or table (if it’s a table-valued function).
Parameters: Only input parameters.
Side Effects: Cannot modify data (read-only).
Usage: Can be called in queries (e.g., SELECT, WHERE, JOIN) and can be used as part of expressions.

Modification of Data: Functions cannot modify data, while stored procedures can.
Return Type: Functions must return a value; stored procedures do not have to.
Execution in Queries: Functions can be used in SELECT or WHERE clauses, but stored procedures cannot.

In short, use stored procedures for batch operations and functions for computations or returning a single value in a query.

![[]]