Intermediate LevelSQL & MySQL
What is the difference between Stored Procedures and User-Defined Functions (UDFs)?
❓ Interview Prompt: Compare Stored Procedures (`CALL`) vs User-Defined Functions (`UDF`), side-effects, transaction control, and invocation contexts.
Comparing server-side database procedural objects:
•
Stored Procedure ('CALL proc()'): Executable database script that can take 'IN', 'OUT', 'INOUT' parameters, perform DML mutations, manage transactions ('COMMIT'/'ROLLBACK'), and return multiple result sets. Cannot be embedded inside a 'SELECT' statement.
•
User-Defined Function ('UDF'): Computes and returns a single scalar value or table expression. Can be invoked directly inside 'SELECT' or 'WHERE' clauses. Must NOT modify database state or manage transactions!
Code Implementation Example
sql
DELIMITER //
CREATE PROCEDURE ProcessMonthlyBilling(IN target_month INT, OUT processed_count INT)
BEGIN
START TRANSACTION;
UPDATE invoices SET status = 'PAID' WHERE month = target_month;
SELECT COUNT(*) INTO processed_count FROM invoices WHERE month = target_month;
COMMIT;
END //
DELIMITER ;💡Creating a transactional MySQL Stored Procedure with IN and OUT parameters.
🎯 Interview Key Takeaway
Use Stored Procedures for complex multi-statement transactional operations, and Functions for reusable scalar calculations inside queries.
#SQL#Stored Procedures#Functions#UDF#Intermediate
Back to all SQL & MySQL Questions→