What is a Stored Procedure?
A stored procedure is a precompiled set of one or more SQL statements that can be executed on demand. Stored procedures allow for code reuse, improved performance, and simplified management of complex SQL operations. They can take input parameters and return values or results.
Benefits of Stored Procedures:
- Modular: Encapsulate reusable logic and business rules.
- Performance: Since they are precompiled, they tend to execute faster than dynamic queries.
- Security: You can grant users permission to execute stored procedures without giving direct access to the underlying tables.
1.1 Creating Stored Procedures
To create a stored procedure in SQL, use the CREATE PROCEDURE statement, followed by the procedure’s name and any parameters it might accept.
Syntax:
CREATE PROCEDURE procedure_name ([parameters])
AS
BEGIN
-- SQL statements
END;
Example: Creating a simple procedure to retrieve all products in a given category.
CREATE PROCEDURE GetProductsByCategory (@categoryName VARCHAR(100))
AS
BEGIN
SELECT product_name, price
FROM Products
WHERE category = @categoryName;
END;
This stored procedure accepts one parameter (@categoryName) and retrieves the product names and prices from the Products table for that category.
1.2 Modifying Stored Procedures
To modify an existing stored procedure, you typically use the ALTER PROCEDURE statement. However, the method for altering stored procedures varies by SQL database. In some systems (like MySQL), you must drop and recreate the procedure if you need to change it.
Syntax (for SQL Server):
ALTER PROCEDURE procedure_name ([parameters])
AS
BEGIN
-- SQL statements
END;
If you’re working with MySQL or other databases that do not support ALTER PROCEDURE, you may need to drop and recreate the procedure like this:
DROP PROCEDURE IF EXISTS procedure_name;
CREATE PROCEDURE procedure_name ([parameters])
AS
BEGIN
-- SQL statements
END;
1.3 Calling Stored Procedures
You can call a stored procedure using the EXEC or CALL statement, depending on the database you’re using.
SQL Server Example:
EXEC GetProductsByCategory 'Electronics';
MySQL Example:
CALL GetProductsByCategory('Electronics');
