How to Create Stored Procedure in MySql Server,Simple stored procedure with output parameter examples.

Most of the peoples who are familiar with database operations know what is a stored procedure, For the beginner's we are mentioning the definition " Its a set of SQL statements with an assigned name,its in a compiled form we can share it with many programs".
Consider we want to insert the details of a registration form into a table in  MySQL Database. Let see how we are going to insert it through stored procedure.
In Mysql stored procedure called as Routines.

If you are using MySql workbench you can see table,views,routines for a database. so right click on the Routines and create a procedure with any name. I have created an sp with name `registrationform`  now I can see my sp in routines list. so  I have right click on that routine and selected alter procedure. There I will write my queries as per my requirement.
If you are using PhpMyadmin select you database go to Routine menu and click on that, the you will see a Add Routine button click on that and create your sp there.

So here I need to pass Firstname,LastName, Username and Password to my table using submit button click there I will call my  stored procedure as per my code syntax, whether its PHP,.net,java etc.

So here I have 4 Input parameters.

So here is our stored procedure.


/* code starts here */

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `registrationform`(In FirstName varchar(20),In Lastname varchar(20),In userName varchar(50),In Pwd varchar(50))
BEGIN

/* Inserting Values into Table */
Insert into tbl_login(`FirstName`,`LastName`,`UserName`,`Password`) values (FirstName,Lastname,userName,Pwd);

END

/* Code Ends Here */
Here we are Inserting FirstName,LastName,UserName,Password details  into tbl_login .
you can call this stored procedure like this and check whether its getting inserted in table or not.

Method for calling a stored procedure.


call registrationform('tony','tom','tonytom','testing');

These 4 parameters are the input parameters that's why  we are passing that value while calling the sp itself.
Hope that you have understood it.

consider you want to get an acknowledgement after insertion so you have to use an out parameter in same stored procedure. so your sp now become like this.

/* code starts here */

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `registrationform`(In FirstName varchar(20),In Lastname varchar(20),In userName varchar(50),In Pwd varchar(50),Out ack int)
BEGIN

/* Inserting Values into Table */
Insert into tbl_login(`FirstName`,`LastName`,`UserName`,`Password`) values (FirstName,Lastname,userName,Pwd);
set ack=1;

END




/* code ends here /*
Here we have one additional parameter all other parameters we are declared like "In Param name datatype", but the fifth parameter is out parameter so we declared as "Out Param Name Datatype"

procedure to calling a stored procedure which is having an output parameter.

call registrationform('tony','tom','tonytom','testing',@x);
select @x;
so now it will return 1 if its inserted successfully in database.