Stored procedures are precompiled SQL code that can be executed repeatedly. Improve performance and maintainability.

Create Procedure (MySQL)

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN customerId INT)

BEGIN

SELECT * FROM orders WHERE customer_id = customerId;

END //

DELIMITER ;

Call Procedure

CALL GetCustomerOrders(123);

Procedure with Output

DELIMITER //

CREATE PROCEDURE GetOrderTotal(IN orderId INT, OUT total DECIMAL(10,2))

BEGIN

SELECT SUM(amount) INTO total FROM order_items WHERE order_id = orderId;

END //

DELIMITER ;

Procedure with Logic

DELIMITER //

CREATE PROCEDURE UpdateStock(IN productId INT, IN quantity INT)

BEGIN

UPDATE products SET stock = stock - quantity WHERE id = productId;

IF (SELECT stock FROM products WHERE id = productId) < 10 THEN

INSERT INTO alerts (message) VALUES ('Low stock alert');

END IF;

END //

DELIMITER ;

Drop Procedure

DROP PROCEDURE IF EXISTS GetCustomerOrders;

Generate Procedures

AI2sql helps write stored procedures from descriptions.