What is the stored procedure, and how do you create one in MySQL?

Question

Accepted Answer

A stored procedure is a named, precompiled block of SQL logic saved in the database that can be executed repeatedly by applications passing in parameters. It centralizes reusable logic on the database side instead of duplicating that logic across application code.

Key Points:
• Reusability: the logic is written once and invoked with a simple CALL statement wherever needed.
• Performance: less SQL text needs to travel over the network per call, and the database can optimize execution.
• Consistency: every caller applies exactly the same logic, reducing the risk of divergent business rules across applications.
• Stored procedures can accept IN, OUT, and INOUT parameters and can perform multiple statements, unlike a function which must return a single value.
• Overuse can make business logic harder to version-control and test compared to keeping it in application code.

Example:
An e-commerce application frequently needs to apply a discount to an order total; rather than repeating that calculation in every service that processes orders, it defines an ApplyDiscount procedure once and calls it with the order amount and discount rate.

Code Example:
```
DELIMITER //
CREATE PROCEDURE ApplyDiscount(
    IN orderAmount DECIMAL(10, 2),
    IN discountRate DECIMAL(5, 2)
)
BEGIN
    DECLARE finalAmount DECIMAL(10, 2);
    SET finalAmount = orderAmount - (orderAmount * discountRate / 100);
    SELECT finalAmount AS Final_Amount;
END //
DELIMITER ;

CALL ApplyDiscount(1000.00, 10.00);
```

Interview Tip:
A concise interview answer is:

"A stored procedure is precompiled SQL logic saved in the database that I can call repeatedly with parameters, using CREATE PROCEDURE and then CALL. I'd use one to centralize logic like applying a discount to an order total, so every caller gets consistent behavior and I'm not duplicating the calculation across the application."