Stored Procedures are simple sql statements that are already processed and stored at server end. This increases the performance in terms of time taken to fetch data as minimum information will get passed through network. The other advantage that stored procedures have is the security aspect, we don’t have to write Sql Query at source code, we will now directly call the stored procedure and MySql will execute the query at his end. In this article we will learn how to create stored procedures in MySql, though i am explaining very simple query executed inside stored procedure but will tell you how we can use Stored Procedure in MySql..

To simplify this tutorial we will go step by step:

We will be working on a test table for our procedure example:
Create Test Table Syntax:

	CREATE TABLE test(id int NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(100), designation varchar(100))

Now we will insert some records inside this table:

	INSERT INTO test(name, designation) VALUES('ABC','Software Engineer');
	INSERT INTO test(name, designation) VALUES('PQR','Team Leader');

	DROP procedure IF EXISTS test;

Create New Stored Procedure in MySql:

	CREATE procedure test()
	BEGIN
	    SELECT name, designation FROM test;
	END

All the sql statements that needs to be executed while executing procedures should be between BEGIN and END.

Calling Stored Procedure From MySql:

	call test();

On executing the above query we get following output:

	CREATE procedure test1(desg varchar(100))
	BEGIN
	    SELECT name, designation FROM test WHERE designation=desg;
	END

Calling Parameterized Stored Procedure From MySql:

	call test1('Team Leader');

On executing the above query we get following output:

Popular Articles:

• MySql – Working With Stored Procedure
• MySql Batch Insert Using PHP
• Installing mytop on Linux
• Database Class in PHP5
• MySql Prepared Statement in PHP

Subscribe to my RSS feed.