What is a stored procedure in MySQL?
Why Interviewers Ask This
This is a classic screening question for MySQL / SQL roles. Hiring managers ask it early in interviews to gauge your baseline understanding and determine if you can communicate technical concepts clearly.
Answer
A stored procedure is a named set of SQL statements stored in the database and executed on the server side. It can contain SQL queries, flow control (IF/ELSE, WHILE, LOOP), variables, and error handling. Creating a procedure: DELIMITER // CREATE PROCEDURE GetUserOrders(IN userId INT) BEGIN SELECT * FROM orders WHERE user_id = userId; END // DELIMITER ;. Call it: CALL GetUserOrders(5);. Benefits: (1) Performance — parsed and compiled once, cached execution plan; (2) Reduced network traffic — one call executes multiple statements server-side; (3) Security — grant EXECUTE permission on the procedure without granting table access; (4) Reusability — encapsulate complex business logic once. Parameter types: IN (input — passed by value), OUT (output — returns a value to caller), INOUT (both). Limitations: harder to version control than application code, database-specific syntax (not portable), debugging is more complex. Modern applications often prefer to handle business logic in the application layer (Node.js, Laravel) and use the database purely for storage.
Common Mistake
Rushing to answer is a common mistake. Take two seconds to structure your response: definition → example → trade-off. This structure makes complex MySQL / SQL answers easy to follow.