Conditional Execution with MySql Procedures

Conditional execution is the great way to control the execution of our stored procedure. Conditional  execution can be implemented very easily like any other programming language in MySql as well.It can be achieved by using IF ELSE or CASE statement. Although here we are taking example of IF Else only. In our example here we are calculating discounted cost based on their normal cost. I.e, if normal cost is greater than 1000 then discount will be 8 percent, if normal cost is greater than 500 then discount will be 9% . You can implement your own logic in same way.

Example of conditional execution of statements in mysql procedure are here:

DELIMITER $$

DROP PROCEDURE IF EXISTS discounted_cost$$

CREATE PROCEDURE discounted_cost (normal_cost NUMERIC(6,2),OUT discount_cost NUMERIC(6,2))

BEGIN

IF(normal_cost >1000) THEN

SET discount_cost = normal_cost*.8;

ELSEIF(normal_cost>500) THEN

SET discount_cost = normal_cost*.9;

ELSE

SET discount_cost = normal_cost;

END IF;

END$$

DELIMITER ;
<pre><span style="color: #000000;"><span style="font-family: Consolas,Monaco,Courier New,Courier,monospace;"><span style="font-size: x-small;">

To excute this stored procedure simply run below query in your query browser.

</pre>
CALL discounted_cost(700.00,@new_cost);

SELECT @new_cost;
<pre><span style="color: #000000;"><span style="font-family: Consolas,Monaco,Courier New,Courier,monospace;"><span style="font-size: x-small;">
 

 
So at a conclusion we can say conditional expressions are used to test the truth of condition that we provide. And MySql also support it as other programming languages support it.
Related posts:
Remove duplicate records using mysql query
How can we see all mysql function availabel on any database
Exporting Importing MySql database using mysqldump in Linux
The following two tabs change content below.
Bio
Latest Posts
Chandra Shekhar
GCP Architect
Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video Conferencing and social media domain. The motive of creating cutehits was just to share the knowledge/solutions I get to know during my day to day life so that if possible I can help someone for same problems/solutions. CuteHits.com is a really a very effort  for sharing knowledge to rest of the world. For any query/suggestion about same you can contact me on   below details:-

Email: shekharmca2005 at gmail.com

Phone: +91-9560201363

				
Latest posts by Chandra Shekhar (see all)
			

				

					Best practices for micro service design - January 23, 2022 
				
				

					Spring Boot - January 23, 2022 
				
				

					Java - January 23, 2022