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
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
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).
The pl/sql execution context does not include Role. So your risght have been delegated by a role. The Pl/SQL doesn't see it.
So, first, you can try to give the right to alter the user directly not via a role.
2nd, i would try this option on the package definition (auhtid) which is explained here
Best Answer
I think you're mixing up Java (other language?) with MySQL's stored procedure language. Look here and here. Check out this by Guy Harrison, the guy who wrote this book.