2007-09-11

Use stored procedures

Some people complain about stored procedures as bad because they inject business logic into the database.
My answer to this is that then the stored procedures are used the wrong way.

Stored procedures should be used to get data in and out the database.  Used this way they represent the holes in and out of the database.
Name the stored procedures cunningly and you have an easy way to find the only ways to insert data into the customer table, the only way to update the order table and only ways to select multiple rows from role.

I have not found the perfect naming scheme.  Anyone?

1 comment:

Manuel Anaya said...

Good day, excellent blog!
I consider stored procedures are a good way to keep your application clean by storing the data access layer logic and operations right in where the data is. In the other hand, keeping your code in 2 different environments would require one and the full developer team to be disciplined and consistent with parameters, procedures naming, data retrieval and good testing.
For me a good notation would be to use, first, the entity to which the procedure is related to, then the functionality(ies) and finally the parameters (consider Hungarian notation as well).

form_add_customer
form_add_order
form_update_order_by_customerName
form_update_order_by_id
user_add
user_update_password

In my opinion this is a fast and clear way to find where is the procedure and find out what in God's name it is supposed to do.
M.-