Hi Devs in this guide we are going to see what is stored procedure in sql, So Basically when you use a query again and again then there you can use stored procedure. You create a procedure with query and call the stored procedure every time instead of calling query again and again.
Here is an example
Create a Stored Procedure
We can create store procedure as given in this statement
DELIMITER $$ CREATE PROCEDURE user_names_procedure() BEGIN SELECT DISTINCT first_name as name FROM users; END $$ DELIMITER ;
After executing this statement you will be able to see stored procedure created.
Now we can call this stored procedure
Calling A Stored Procedure
call user_names_procedure;
It will return us data
Passing Arguments in Stored Procedures
This is interesting, We can pass arguments in stored procedure, let’s same example again this time with argument.
DELIMITER $$ CREATE PROCEDURE user_names_procedure(IN user_age int) BEGIN SELECT first_name as name FROM `users` where age>user_age; END $$ DELIMITER ;
Here we passed “user_age” as argument and its type is “integer”.
So let’s see how we will call this procedure
call user_names_procedure(50);
Here we passed 50 as “user_age”.
so we get users list where users has age more than 50. as 50 passed as argument.
IN AND OUT Arguments
Ok if you noticed in stored procedure we passed “user_age” argument as “IN”.
CREATE PROCEDURE user_names_procedure(IN user_age int)
Now we can also pass out argument as well.
so it will look like this
CREATE PROCEDURE user_names_procedure(IN user_age int, OUT argumentName int)
Here we are saying argument is of type “integer”. Let’s see complete example
First i drop the previously created procedure.
DROP STORED Procedure
DROP PROCEDURE user_names_procedure;
Now re-create procedure with output parameter.
DELIMITER $$ CREATE PROCEDURE user_names_procedure(IN user_age int, OUT total_users int) BEGIN SELECT count(*) into total_users FROM `users` where age>user_age; END $$ DELIMITER ;
total_users passed as parameter but also used in query.
Calling the procedure.
call user_names_procedure(50, @users_count); select @users_count;
Here we are getting returned value in variable and then we are selecting it.
DELIMITER : If you are wondering what is this, Then i must say it is used when we work with stored procedures, function and triggers.
Hope this helps