What is a stored procedure in MySQL?
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.