Pro and Cons between Ad Hoc SQL and Stored Procedure
Pro and Cons between Ad Hoc SQL and Stored Procedure
By : Kasim Wirama, MCDBA
Before I give the pro and cons between Ad Hoc SQL and stored procedure, I would like to define what Ad Hoc SQL is. Ad Hoc SQL is SQL statements that is emitted from application into database. They are SELECT, INSERT, UPDATE and DELETE. Ad hoc SQL is generated runtime by application into database. In online and offline discussion, some professionals prefer using ad hoc SQL rather than stored procedure, and the others prefer using stored procedure.
For me, both have advantages and disadvantages, it depends the scenario that directs your architecture. Here are some points I find here.
When you need flexibility with every possible query that comes from application tier into database tier, ad hoc SQL is preferred way to go. Performance might be better in case of you just need necessary columns in SQL statements, not all of them.
When you use ad hoc SQL, you will make your application low cohesion and high coupling. Even small changes will impact to changes to your application tier as well. If you emit some SQL statements as one batch, the complexity will rise, and it will not be easy to maintain. Of course, care should be taken to safeguard the ad hoc SQL from possibility of SQL injection. In case of performance issue in your application, it is harder to do because many possibilities come from ad hoc SQL and you need to change and re-deploy your application.
The good point, when stored procedure is used, is that they provide encapsulation from complex logic. Dynamic SQL can be safely implemented in stored procedure and the problem could be easier to handle because it is localized. You can grant stored procedure only instead of giving user to directly interact with tables in a database. Performance tuning would be easier to do in stored procedure rather in ad hoc SQL.
Do you think there are no cons for using stored procedure? Not really. Stored procedure is more rigid rather that ad hoc SQL so don’t put frequently-changed business logic and rule into stored procedure, better to put them in application tier. You have limited options to make just necessary columns involved dynamically.
Hopefully, it will give you guidance which one is appropriate to implement based on your nature of application.