Stored Procedure in MySQL

Question

I am new to MySQL Stored Procedure, in my first attempt I got the following issues,

Stored Procedure:

Delimiter $$
create procedure accountstatus
(
 IN inmode varchar(27),
 IN AccountStatus_id int,
 IN AccountStatus varchar(255),
 IN CreatedOn datetime,
 IN CreatedBy varchar(255),
 IN UpdatedOn datetime,
 IN UpdatedBy varchar(255),
 IN is_active bit)
 Begin
 if inmode = 'insert'
 then
 insert into accountstatus
 (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
 values
 (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active);
 end if;
 /*update*/
 if inmode = 'update'
 then
 update accountstatus acc
 set
 acc.AccountStatus_id = AccountStatus_id,
 acc.Account_Status = Account_Status,
 acc.CreatedOn = CreatedOn,
 acc.CreatedBy = CreatedBy,
 acc.UpdatedOn = UpdatedOn,
 acc.UpdatedBy = UpdatedBy,
 acc.is_active = is_active
 where
 acc.AccountStatus_id = AccountStatus_id;
 end if;
 /*delete*/
 if inmode = 'delete'
 then
 update accountstatus acc
 set
 acc.AccountStatus_id = AccountStatus_id,
 acc.is_active = 0
 where
 acc.AccountStatus_id = AccountStatus_id;
 end if;
 /*select*/
 if inmode = 'select'
 then
 select * from accountstatus acc
 where
 acc.AccountStatus_id = AccountStatus_id;
 end if;
end

When I execute these statements I got the following output:

STATEMENT

call accountstatus ('insert', 1, 'accepted', current_date(),'rathish', current_date(), 'raj', 1); 

OUTPUT

call accountstatus ('insert', 8, 'accepted', current_date(),'rathish', current_date(), 'raj', 1)
1 row(s) affected

Similarly I inserted some values to this table, but when I tried to execute the following update statement, values in the Account_Status became null as shown below,

STATEMENT

call accountstatus ('update', 8, 'accepted', current_date(),'raj', current_date(), 'raj', 1);

OUTPUT

call accountstatus ('update', 8, 'accepted', current_date(),'raj', current_date(), 'raj', 1)
1 row(s) affected

But, when I execute the following query statement I got the unexpected output as follows,

enter image description here

1. What is error in this procedure?
2. Is there any other ways to implement this procedure?
3. How it will effect the performance of the database?

Thanks in advance.

Answer 1

QUESTION #1

What is error in this procedure?

You seem to have some scope confusion on the variables

ANSWER TO QUESTION #1

PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.

SOLUTION : Change the names of the parameters so that they are distinct from the column names

create procedure accountstatus
(
 IN inmode varchar(27),
 IN given_AccountStatus_id int,
 IN given_AccountStatus varchar(255),
 IN given_CreatedOn datetime,
 IN given_CreatedBy varchar(255),
 IN given_UpdatedOn datetime,
 IN given_UpdatedBy varchar(255),
 IN given_is_active bit)
 Begin
 if inmode = 'insert'
 then
 insert into accountstatus
 (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
 values
 (given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
 end if;
 /*update*/
 if inmode = 'update'
 then
 update accountstatus acc
 set
-- acc.AccountStatus_id = given_AccountStatus_id, <- Not Needed for UPDATE
 acc.Account_Status = given_Account_Status,
 acc.CreatedOn = given_CreatedOn,
 acc.CreatedBy = given_CreatedBy,
 acc.UpdatedOn = given_UpdatedOn,
 acc.UpdatedBy = given_UpdatedBy,
 acc.is_active = given_is_active
 where
 acc.AccountStatus_id = given_AccountStatus_id;
 end if;
 /*delete*/
 if inmode = 'delete'
 then
 update accountstatus acc
 set
-- acc.AccountStatus_id = given_AccountStatus_id, <- Not Needed for DELETE
 acc.is_active = 0
 where
 acc.AccountStatus_id = given_AccountStatus_id;
 
 end if;
 /*select*/
 if inmode = 'select'
 then
 select * from accountstatus acc
 where
 acc.AccountStatus_id = given_AccountStatus_id;
 end if;
end

CAVEAT : Please note that I commented out two lines

QUESTION #2

Is there any other ways to implement this procedure?

ANSWER TO QUESTION #2

You could use triggers

• Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
• Apr 06, 2012 : How to speed up audit trigger?

QUESTION #3

How it will effect the performance of the database?

ANSWER TO QUESTION #3

Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible

• Aug 11, 2011 : Policies RE database triggers in well-designed applications?
• Aug 12, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
• Dec 23, 2011 : How to set up triggers in MySQL for copying a field

EPILOGUE

The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.

Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).

• Rathish Kumar B: Thanks for your answer and suggestions, I just need to know whether using the parameter name same as column name will cause issues? Thanks in advance.

Answer 2

The error is that you are using undeclared parameter name for AccountStatus values.

Below is your declaration for AccountStatus:

IN AccountStatus varchar(255)

But in your update and insert statement you are using Account_Status. This is taking from your database column value.