Mysql – Stored Procedure – MySQL


I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to implement the following logic,

For example:
Business Type Table
Business Type Table

Stored Procedure

Business Type Stored Procedure

As per my knowledge I thought, this procedure will work fine. While I call the procedure name as follows,

Call businesstype('insert', 1, 'Online Marketing', datetime(),'raj', datetime(), 'raj', 1);
Every time needs to call this procedure with all its parameters, but developers need to call the procedure with required fields, for example they need to call only the ID (PK) to perform delete function, but it required some values (NULL) for remaining parameters.
How to perform this action? I am just a starter, I am expecting some best practices to implement this, also I need to know whether the given table design and procedure will meet the industry expectations? is it good to implement in this way?

Thanks in advance.

Best Answer

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.