• Home
  • ZarxBiz
  • Forum
Igniter Code
IgniterCode.com === Zarx.Biz
ReactJS

ecommerce cart react source code 2022 [ For Beginners ]

October 8, 2022 0 comment

Why and how to use sql views 2022

August 22, 2022 0 comment

how to import csv to mysql using php

August 22, 2022 0 comment
PHP

Code faster using visual studio Updated 2022

July 31, 2022 0 comment

Web socket crash course 2022 updated step by step

July 3, 2022 0 comment

How to use stored procedure in sql updated 2022

September 3, 2022 0 comment
MySQL

Must know mysql functions updated 2022

September 3, 2022 0 comment

PHP Composer Autoload Complete Guide 2022

August 13, 2022 0 comment
Tag:

how to use stored procedure in sql

Stored procedure in mysql with example [ For beginners ] 2022
MySQL

How to use stored procedure in sql updated 2022

by Manu September 3, 2022
written by Manu

 

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

September 3, 2022 0 comment
0 FacebookTwitterPinterestEmail

Recent Posts

  • ecommerce cart react source code 2022 [ For Beginners ]
  • How to use stored procedure in sql updated 2022
  • Must know mysql functions updated 2022
  • Why and how to use sql views 2022
  • how to import csv to mysql using php

Recent Comments

No comments to show.
  • Facebook
  • Twitter
  • Youtube
  • Email

@2022 - All Right Reserved


Back To Top
Igniter Code
  • Home
  • ZarxBiz
  • Forum