Stored Procedures vs Functions
Stored Procedures vs Functions
Both are stored, precompiled SQL routines, but their execution contexts and capabilities differ fundamentally.
Key Differences
| Feature | Stored Procedure | User-Defined Function (UDF) |
|---|---|---|
| Invocation | CALL or EXECUTE |
Inside SELECT / WHERE clauses |
| Return Value | 0, 1, or multiple result sets | Must return scalar value or table |
| DML Modifications | Allowed (INSERT/UPDATE/DELETE) | Read-only in standard queries |
| Transaction Support | Supports COMMIT & ROLLBACK | Cannot manage transactions |
| Usage Context | Business logic, batch jobs, ETL | Calculations, transformations |