Skip to main content

How to create and call stored procedure in MySQL with IN and OUT parameters


Create and Call MySQL stored procedure with IN Parameters

Here is the command to create a MySQL stored procedure with one IN parameter, here we are getting total number of employee by department, dept_id is a foreign key from department table.
mysql> DELIMITER //
mysql> create procedure usp_totalEmployeeByDeparment(IN id INT)
    -> begin
    -> select count(*) as total from employee where dept_id = id;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
We have first changed delimiter as // to mark end of stored procedure, and then reverted it back to previous delimiter. Also using “usp” as prefix for user defined stored procedure is one of the SQL best practices to separate system and user stored procedures. Now you can call this stored procedure from MySQL command prompt as :
mysql> call usp_totalEmployeeByDeparment(2);
+-------+
| total |
+-------+
|     1 |
+-------+
1 row in set (0.06 sec)

Creating and Calling MySQL stored procedure with IN and OUT parameters

In this MySQL example, we have created stored procedure usp_GetEmployeeName which takes one IN and one OUT parameter. While calling this stored procedure, you need to pass two parameters, id and name. One would be  input parameter id and other would be output parameter to store result.
mysql> DELIMITER //
mysql> create procedure usp_GetEmployeeName(IN id INT, OUT name VARCHAR(20))
    -> begin
    -> select emp_name into name from employee where emp_id = id;
    -> end//
Query OK, 0 rows affected (0.52 sec)
mysql> DELIMITER ;
mysql> call usp_GetEmployeeName(103, @name);
Query OK, 1 row affected (0.05 sec)
Calling stored procedure from MySQL command line:
mysql> select @name;
+-------+
| @name |
+-------+
| Jack  |
+-------+
1 row in set (0.00 sec)
That's all on How to create and call MySQL stored procedure from command line. In this MySQL tutorial, we have seen examples of creating stored procedure using IN and OUT parameters. These are one of the best way to remember and recall syntax of stored procedure in MySQL database.

Popular posts from this blog

Draw pyramid shape of stars in PHP using loop structure Write down the code in PHP using loop structure to draw the 3 pyramid shapes of stars as given bellow:

ravimanephpmysql

// problem 01 solution
for($i=5;$i>=1;$i--)
{
echo  str_repeat('*',$i);
echo "<br />";
}

echo "<hr />";

// problem 02 solution

for($i=1;$i<=5;$i++)
{
echo  str_repeat('*',$i);
echo "<br />";
}


echo "<hr />";
// problem 03 solution

$j=1;

for($i=5;$i>=1;$i--)
{
//echo  str_repeat("&nbsp;",$i-1);
echo  str_repeat('*',$j++);
echo "<br />";
}

Google reCAPTCHA with PHP tutorial

Login to your Google account and submit the form. Once submit, Google will provide you following two information. Site keySecret key

Integrate it into your website. To integrate it into your website you need to put it in client side as well as in Server side. In client HTML page you need to integrate this line before <HEAD> tag.
<script src='https://www.google.com/recaptcha/api.js'></script> And to show the widget into your form you need to put this below contact form, comment form etc.
<divclass="g-recaptcha" data-sitekey="== Your site Key =="></div> When the form get submit to Server, this script will send ‘g-recaptcha-response’ as a POST data. You need to verify it in order to see whether user has checked the Captcha or not. Simple comment form with Google reCAPTCH:Index.html <html>
<head>
<title>Google recapcha demo - Codeforgeek</title>
<scriptsrc='https://www.google.com/recaptcha/api.js'&g…

Simple Laravel CRUD with Resource Controllers

Creating, reading, updating, and deleting resources is used in pretty much every application. Laravel helps make the process easy using resource controllers. Resource Controllers can make life much easier and takes advantage of some cool Laravel routing techniques. Today, we'll go through the steps necessary to get a fully functioning CRUD application using resource controllers. For this tutorial, we will go through the process of having an admin panel to create, read, update, and delete (CRUD) a resource. Let's use nerds as our example. We will also make use of Eloquent ORM. This tutorial will walk us through: * Setting up the database and models * Creating the resource controller and its routes * Creating the necessary views * Explaining each method in a resource controller Table of ContentsGetting our Database ReadyEloquent Model for the NerdsCreating the ControllerSetting Up the RoutesThe ViewsMaking It All Work TogetherConclusion To get started, we will need the controll…