What is Difference between STORED PROCEDURE vs FUNCTION?

The difference between a Stored Procedure and a Function mainly comes down to how they behave, what they return, and how you use them in SQL.

1. Basic Definition

• Stored Procedure: A set of SQL statements that performs a task (can include logic, loops, transactions, etc.).
• Function: A routine that must return a value and is usually used within queries.

2. Return Value

Stored Procedure:

• May or may not return a value
• Can return multiple values using output parameters

Function:

• Must return exactly one value (scalar or table)

3. Usage in Queries

Stored Procedure:

• Cannot be used directly inside SELECT, WHERE, etc.
• Called using CALL or EXEC

Function:

• Can be used inside SQL statements
  • Example:

SELECT my_function(column_name) FROM table;

4. Data Modification

Stored Procedure:

• Can perform INSERT, UPDATE, DELETE operations
• Can manage transactions (COMMIT/ROLLBACK)

Function:

• Typically cannot modify database state (in most DB systems)
• Mainly used for calculations or data transformation

5. Parameters

Stored Procedure:

• Supports IN, OUT, INOUT parameters

Function:

• Usually supports only input parameters

6. Execution

Stored Procedure:

CALL procedure_name();

Function:

SELECT function_name();

7. Use Case

Stored Procedure:

• Complex business logic
• Batch processing
• Data manipulation

Function:

• Calculations
• Formatting values
• Reusable logic in queries

Quick Summary