Stored procedures or Embedded SQL???

One of the clients I worked for didn’t allow it’s developers to use embedded SQL in their sourcecode. Only the usage of stored procedures was allowed. Applications only got the database authorization to call stored procedures, nothing else. To me this is the most optimal use of database security!

The DBA department made one stored procedure per table to select everything and one to select records on bases of their primary key. All other stored procedures that were necessary for an application were made by developers, but before they could be used in a production environment the DBA’s reviewed them. This way the DBA’s could keep an eye on how different tables were used and how this was affecting the database / DBMS. If necessary they optimized the stored procedures or just sent it back to the developers to have them optimize it themselves. In general this made for optimized stored procedures with a uniform way of coding in them. I liked this way of working and try to do this also with other clients, if they allow me to do this of course.

In general for me the advantages of stored procedures are:

1. Security (only expose stored procedures to the big bad world outside, nothing else)
2. Speed; because the DBMS optimizer makes a one time plan for execution (this has advantages and disadvantages but that’s another discussion altogther)
3. Reusable code / procedures.
4. Reviewable code. Reviewable by DBA’s that have the right knowledge for optimizing stored procedures. No .NET or other knowledge is necessary for these reviews!

Disadvantages:
….
So according to me we should use stored procedures more often!