Q31: What is the difference between a Stored Procedure and a User-Defined Function?
- Stored Procedure: A compiled collection of SQL queries designed to execute administrative business routines. Can accept input/output parameters, perform full DML operations, control transactions (
COMMIT/ROLLBACK), and return zero or multiple result sets. Cannot be called inside aSELECTstatement. - User-Defined Function (UDF): A reusable calculation routine designed to compute and return a single value or table. Must be deterministic or safe, cannot alter transaction states (
COMMITis forbidden), and can be used directly insideSELECT,WHERE, andJOINclauses.
Key Takeaways
- Functions return values and can be called inside SELECT statements.
- Procedures execute standalone routines and manage transactions explicitly.