Stored Procedures

What is a Stored Procedure?

A Stored Procedure is a prepared SQL code that you can save, so the code can be reused over and over again. You can also pass parameters to a stored procedure.

Benefits

• Reuse: Write once, call multiple times.
• Performance: Pre-compiled and cached by the database.
• Security: Grant permission to execute procedure without giving access to tables.

Creating a Procedure

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

(Syntax varies slightly between MySQL, PostgreSQL, and SQL Server)

Example (MySQL)

DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM Customers;
END //
DELIMITER ;

Executing a Procedure

To run the procedure:

CALL GetAllCustomers();

Procedures with Parameters

DELIMITER //
CREATE PROCEDURE GetCustomersByCity(IN cityName VARCHAR(255))
BEGIN
    SELECT * FROM Customers WHERE City = cityName;
END //
DELIMITER ;

Call it:

CALL GetCustomersByCity('London');

[!IMPORTANT] Stored Procedures are highly database-specific. The syntax for MySQL, PostgreSQL (CREATE FUNCTION), and SQL Server (CREATE PROCEDURE) differs significantly.

Dropping a Procedure

DROP PROCEDURE IF EXISTS SelectAllProducts;