This is not a new topic, but this is very important topic for all Database Professional and Software Architecture. All most every Database Professionals had faced same discussion on their company that which one is a better Stored Procedure or Inline Ad Hoc SQL.
As a Database Architecture, I suggest to use only Stored Procedure. I know that some of new RDBMS versions support Ad Hoc Statement level optimization, but still I am with only Stored Procedure.
The truth about to using Stored Procedure instead of Inline SQL.
One Stored Procedure can use in multiple code base. Same Ad Hoc query you cannot use multiple times.
Using Stored Procedure, DBA can easily troubleshoot long running queries for performance tuning.The DBA can easily optimized query without accessing application source code.The DBA can also easily measure the Memory pressures and can tune the query accordingly.
The DBA can control what queries the application can run, what tables it can access. If the developers are writing Ad Hoc queries in their application, every time they will come to my desk and asking for different table access permission.
Stored Procedure is a very easy way to find what kind of queries are running on my database server. To find Application running Inline SQL is quite difficult to trace and even multiple application also using the same kind of Inline SQL.
Using Stored Procedure, you can easily prevent SQL Injection even with dynamic SQL. In the Inline SQL statements, the DBA has no control to prevent SQL Injection.
The DBA can also control many things as per table data, like data types, database global variables, parameters, temp variables.
The RDBMS stores query execution plans in the reserved cache area so running query frequently doesn’t require parsed and compiled again and again.
The RDBMS also stored frequently query results into the cache. If you are using Inline SQL, you cannot use the caching feature of RDBMS.
In the database system, table or transaction lock is a very common thing and it is required, full concentration of DBA to resolve this. Imagine about Inline SQL, it is very difficult to resolve this kind of issue.
The DBA has no control on application code so imagine that if a developer write ” SELECT * ” in their Inline SQL. Anytime Database Schema or table structure can be changed and at that time it is very difficult to identify and change this kind of Ad-Hoc SQL.
Database Documentation is one of most important task and responsibility of the DBA. If we are managing all queries and requirements using Database Objects, it is very easy to generate or manage Database Level Diagram with the full details of Database Object.
Using Stored Procedure, DBA can handle security very well like hiding the Database Code at Application Level.