MySql – Working With Stored Procedure
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');
Now our table will have following data:
| Id | Name | Designation |
| 1 | ABC | Software Engineer |
| 2 | PQR | Team Leader |
Drop Already Created Stored Procedure in MySql:
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:
| Name | Designation |
| ABC | Software Engineer |
| PQR | Team Leader |
Create Stored Procedure with Parameter in MySql:
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:
| Name | Designation |
| PQR | Team Leader |
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.















Nicely explained….
hi,
Nicely Explained but it is not working on mysql 5.0 show following error-
“#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” ”
can u explain me where i wrong?
I have to say, that I could not agree with you in 100%, but it’s just my IMHO, which indeed could be very wrong.
p.s. You have a very good template for your blog. Where have you got it from?
Dear Hitesh
Its not wroking showing below error :
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 3