Stored Procedure - MySQL

Question

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

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.

Answer 1

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.

Answer 2

This should be three procedures.

A stored procedure on a DB, when used as you're trying to, should be like a method on a class: Have a well defined purpose and name, do one thing, and return something predictable.

There should be one procedure for select which can be given one optional ID parameter and will return as few rows as possible (filtered by ID, or not, based on availability). You should never use select *! If you assume that use of * in an SQL query is a bug you will not often be wrong. (Obvious exception: count(*)is pretty harmless). Instead, list each column individually each time. This may seem like a pain now, but if the structure of the table were ever to change and you hadselect *` the application would immediately fail.

There should be one procedure for update and insert which requires all fields from the table except ID, which is permitted and optional. If ID is present it the procedure will update, otherwise insert. No resultset should be returned.

There should be one procedure for delete which takes a required ID parameter; it should return no resultset.

This provides a straightforward API that the client developers can use to do what they care about: Read, Write, Delete.