Blog

A stored procedure contains a set of SQL statements and procedural statements. They may contain only procedural statements. The definition of stored procedure is stored in database and the parameters are stored in data dictionary tables.

Advantages

• Stored procedures reduce the network load between the client and the server.
• Provides better security since the data is accessed through stored procedures instead of accessing them directly.
• Gives better maintenance since the business logic is tested and stored in the server.

Creating Procedure

Stored Procedures are created using CREATE PROCEDURE statement.

Syntax

Following is the generic syntax of the CREATE PROCEDURE statement.

CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) 
BEGIN 
   <SQL or SPL statements>; 
END;

Example

Consider the following Salary Table.

The following example creates a stored procedure named InsertSalary to accept the values and insert into Salary Table.

CREATE PROCEDURE InsertSalary ( 
   IN in_EmployeeNo INTEGER, IN in_Gross INTEGER, 
   IN in_Deduction INTEGER, IN in_NetPay INTEGER 
) 
BEGIN 
   INSERT INTO Salary ( 
      EmployeeNo, 
      Gross, 
      Deduction, 
      NetPay 
   ) 
   VALUES ( 
      :in_EmployeeNo, 
      :in_Gross, 
      :in_Deduction, 
      :in_NetPay 
   ); 
END;

Executing Procedures

Stored Procedures are executed using CALL statement.

Syntax

Following is the generic syntax of the CALL statement.

CALL <procedure name> [(parameter values)];

Example

The following example calls the stored procedure InsertSalary and inserts records to Salary Table.

CALL InsertSalary(105,20000,2000,18000);

Once the above query is executed, it produces the following output and you can see the inserted row in Salary table.