A stored procedure is a reusable SQL code that is stored in the database and can be executed with specific input parameters. Below is a basic example of how to create and call a stored procedure.
1.) Creating a Stored Procedure
Let’s create a stored procedure to fetch users by a given status.
DELIMITER // CREATE PROCEDURE GetUsersByStatus ( IN user_status VARCHAR (20)) BEGIN SELECT id, name , email FROM users WHERE status = user_status; END // DELIMITER ; |
DELIMITER //
: Changes the default statement delimiter (;
) to//
so that the procedure body can contain semicolons without terminating prematurely.IN user_status VARCHAR(20)
: Defines an input parameter of typeVARCHAR(20)
nameduser_status
.BEGIN ... END
: Defines the body of the procedure, which contains the SQL logic.
2.) Calling the Stored Procedure
You can call the stored procedure using the CALL
statement.
CALL GetUsersByStatus( 'active' ); |
This will execute the stored procedure GetUsersByStatus
with 'active'
as the input parameter, and it will return all users with the status active
.
3.) Example with Output Parameter
Stored procedures can also return values using output parameters.
DELIMITER // CREATE PROCEDURE GetUserCount ( IN user_status VARCHAR (20), OUT user_count INT ) BEGIN SELECT COUNT (*) INTO user_count FROM users WHERE status = user_status; END // DELIMITER ; |
OUT user_count INT
: Defines an output parameter to store the result.
Call Procedure with Output:
SET @ count = 0; -- Declare a variable to store the output CALL GetUserCount( 'active' , @ count ); SELECT @ count AS ActiveUsers; -- Display the result |
@count
is a session variable that receives the output value from the procedure.
4.) Example with Both Input and Output Parameters
Create Procedure:
DELIMITER // CREATE PROCEDURE CalculateSum ( IN num1 INT , IN num2 INT , OUT sum_result INT ) BEGIN SET sum_result = num1 + num2; END // DELIMITER ; |
Call Procedure:
SET @result = 0; CALL CalculateSum(5, 10, @result); SELECT @result AS SumResult; |
This will return the sum of 5
and 10
as 15
.
Notes:
- Privileges: Ensure the user has sufficient privileges to create stored procedures (
CREATE ROUTINE
privilege). - Error Handling: Advanced procedures can include error handling with
DECLARE
andHANDLER
statements. - Performance: Stored procedures execute on the database server, reducing network latency for complex queries.