how to use stored procedure in sql

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