A stored procedure in SQL is a pre-compiled collection of SQL statements that are stored in the database and can be executed repeatedly to perform specific tasks. Stored procedures allow you to encapsulate logic in the database and reuse it multiple times, making it easier to maintain and enhance the logic over time.
Syntax:
|
1 2 3 4 5 |
CREATE PROCEDURE procedure_name (IN/OUT parameter_name datatype) BEGIN -- SQL statements END; |
Example:
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE get_expensive_cars (OUT total_cars INT) BEGIN SELECT COUNT(*) INTO total_cars FROM cars WHERE price >= 50000; END; |
In this example, a stored procedure named get_expensive_cars is created to count the number of cars in the cars table whose price is greater than or equal to 50000. The stored procedure takes an output parameter total_cars of type INT, which will hold the number of cars. To execute this stored procedure, you can use the following statement:
|
1 2 |
CALL get_expensive_cars (@total_cars); |
Note: The syntax for creating and calling stored procedures may vary depending on the SQL database management system you are using.