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.
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.
Step 1: Create a new test database:
Step 2: Select the newly created database:
Step 3: Create a new table with three example data fields:
Step 4: Define the delimiter as // or any other character other than semicolon (;) (semicolon default statement delimiter):
Step 5: Create a stored procedure to insert data into table student.
Step 6: Redefine the delimiter as semicolon (;):
Step 7: Call stored procedure sp_student:
Step 8: Issue SELECT statement to verify the inserted values.
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.