Home Sql Call Procedure
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Call Procedure

Advertisement
Procedure is a set of SQL statement that perform a logical unit and task.

Call Procedure

     

 Procedure is a set of SQL statement that perform a logical unit and  task. Stored Procedure can be compiled and executed with different parameter that may be combination of input, output and input/output parameter.

Understand with Example

The Tutorial illustrate an example from 'Call Procedure'. In this Tutorial we create a table 'Stu_Table' with table attribute like 'field' and data type respectively. The create table statement is used to create a table 'stu_table '.

Create Table Stu_Table

CREATE TABLE Stu_Table (              
             Stu_Id int,     
             Stu_Name varchar(10),  
             Stu_Class int);

Insert Data Into Stu_Table

The insert into add the records or rows into the table 'stu_table' with given values specified in Query. 

insert into stu_table values(1, 'Komal',10);
insert into stu_table values(2, 'Ajay',10);
insert into stu_table values(3, 'Santosh',10);
insert into stu_table values(4, 'Rakesh',10);
insert into stu_table values(5, 'Bhau',10);

insert into stu_table values(1, 'aaa',11);
insert into stu_table values(2, 'bbb',11);
insert into stu_table values(3, 'ccc',11);
insert into stu_table values(4, 'ddd',11);
insert into stu_table values(5, 'eee',11);

insert into stu_table values(1, 'iii',12);
insert into stu_table values(2, 'jjj',12);
insert into stu_table values(3, 'kkk',12);
insert into stu_table values(4, 'lll',12);
insert into stu_table values(5, 'mmm',12);

Stu_Table

+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1      | Komal    | 10        |
| 2      | Ajay     | 10        |
| 3      | Santosh  | 10        |
| 4      | Rakesh   | 10        |
| 5      | Bhau     | 10        |
| 1      | aaa      | 11        |
| 2      | bbb      | 11        |
| 3      | ccc      | 11        |
| 4      | ddd      | 11        |
| 5      | eee      | 11        |
| 1      | iii      | 12        |
| 2      | jjj      | 12        |
| 3      | kkk      | 12        |
| 4      | lll      | 12        |
| 5      | mmm      | 12        |
+--------+----------+-----------+

Create Procedure

The Drop Procedure delete the procedure 'stu ',if it is present in database. The create procedure create a procedure' stu ' that accept class as input parameter and data type is integer type. The select statement  return you the records from stu_table whose stu_class is any class value passed as parameter. 

DELIMITER $$
DROP PROCEDURE IF EXISTS stu$$
CREATE PROCEDURE stu (class int)
BEGIN
	Select * from stu_table where Stu_Class = class=class;
END$$
DELIMITER ;

Call Procedure

The call stu(10) return you the records from table 'Stu_Table' where stu_class value is '10'.

call stu(10);

Result

+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1      | Komal    | 10        |
| 2      | Ajay     | 10        |
| 3      | Santosh  | 10        |
| 4      | Rakesh   | 10        |
| 5      | Bhau     | 10        |
+--------+----------+-----------+

Call Procedure

call stu(11);

Result

+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1      | aaa      | 11        |
| 2      | bbb      | 11        |
| 3      | ccc      | 11        |
| 4      | ddd      | 11        |
| 5      | eee      | 11        |
+--------+----------+-----------+
Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: December 31, 2008

Ask Questions?    Discuss: Call Procedure  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com