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
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
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