[Solved] How to write a stored procedure in MySQL for insert?

Problem:

When multiple client applications such as web applications, desktop applications and mobile applications written in different languages like php, python, java, objective c, etc. need to perform the same database operation (insert), the same operations done by different client applications and client applications are directly accessing the database table using same SQL statement.

Solutions:

We are implementing stored procedure; here the client applications will simply call the defined stored procedures to perform the database operation (insert). In this post let us see about the syntax of the stored procedure and will see an example of insert operation using stored procedure using MySQL.

Syntax:

mysql> DELIMITER //

mysql> CREATE PROCEDURE Procedure_name(arguments) 

     ->  BEGIN 

     -> 

     -> SQL Statements; 

     -> 

     -> END // 

Query OK, 0 rows affected(0.00 sec) 

mysql > DELIMITER ; 

Example:

Step 1: Create a new test database:

mysql> CREATE DATABASE school;

Step 2: Select the newly created database:

mysql> USE school;

Step 3: Create a new table with three example data fields:

mysql> CREATE TABLE student (name varchar(30), dob date, address varchar(100)); 

Step 4: Define the delimiter as // or any other character other than semicolon (;) (semicolon default statement delimiter):

mysql> DELIMITER //

Step 5: Create a stored procedure to insert data into table student.

mysql> CREATE PROCEDURE sp_student

    -> (

    ->   IN s_name    varchar(30),

    ->   IN s_dob     date, 

    ->   IN s_address varchar(100)

    -> )

    -> BEGIN

    ->     INSERT INTO student(name, dob, address)

    ->     VALUES (s_name, s_dob, s_address);

    -> END //   

Query OK, 0 rows affected(0.00 sec) 

Step 6: Redefine the delimiter as semicolon (;):

mysql> DELIMITER ;

Step 7: Call stored procedure sp_student:

mysql> CALL sp_student ('raj', '1991-09-27', 'California, USA');

Query OK, 1 row affected (0.05 sec)

mysql> CALL sp_student ('steve', '1995-02-19', 'London, UK');

Query OK, 1 row affected (0.05 sec)

Step 8: Issue SELECT statement to verify the inserted values.

mysql> SELECT * FROM student;

SELECT Statement

Possible Error:

Error Code: 1064 You have an error in your SQL statement;

Workaround:

Case 1: You have not defined the delimiter properly (check step 4)

Case 2: You have not clearly differentiated user defined parameter with table parameters (check step 5 – name table variable & s_name is procedure parameter).

Case 3: Check the delimiter at end line of the stored procedure.

Case 4: Check for keywords BEGIN, END and check all the parenthesis are properly closed.

This post will help you to write stored procedure in MySQL for performing insert operation, in the coming post let us discuss more about MySQL functionalities.