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,

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).

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.