This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Sharing not a new topic, but this is an important question for all Database Professional and Software Architecture.
Almost every Database Professionals had faced the same discussion on their company that which one is a better either Stored Procedure or Inline Ad Hoc SQL.
As a Database Architecture, I suggest using only Stored Procedure. I know that some of new RDBMS versions support Ad Hoc SQL Statements very well, but still, I am with only Stored Procedure.
The truth about on using Stored Procedure instead of Inline SQL.
Once you create the Stored Procedure, you can call it anywhere, anytime and any number of times. Same Ad Hoc query you cannot use multiple times.
Using Stored Procedure, DBA can quickly troubleshoot long running queries.The DBA can quickly optimize the query without accessing application source code.
The DBA can also easily measure the Memory utilization 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 me and asking for different table access permissions.
Stored Procedure is a straightforward way to find the what kind of queries are running on my database server. For Inline SQL is quite difficult to trace and even multiple application also using the same type 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 on the SQL Injection.
The DBA can also control many things like table data, data types, global database variables, parameters, temp variables.
The RDBMS stores query execution plans in the reserved cache area so running query frequently doesn’t require to be 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 general thing, and it requires the full concentration of DBA. Imagine about Inline SQL. It is challenging to resolve this kind of issue where DBA feels difficulty in finding the multiple running transactions and their respective path/sequence.
The DBA has no control on application code so imagine that if a developer writes ” SELECT * ” in their Inline SQL. Anytime Database Schema or table structure can be change, and at that time their inline SQL Queries will fail.
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 required details
Using Stored Procedure, DBA can handle security very well like hiding the Database Code from Application.
