Inline/Dynamic SQL vs Stored Procedures (Just 5 short summarized points)

After reading some articles and discussions on this topic, my conclusion is:

It is a myth to use all inline/dynamic SQL for all the queries in the application.

It is a myth to use all Stored Procedure for all the queries in the application.

Use Stored Procedure when the query is very complicated, easily will need changes and with many business logics. Reason is easier maintenance. Business logic easily change. Changes in Stored Procedure does not required re-compile of code.

Use Stored Procedure when the query will take long time to complete it. Reason is better performance.

Other that the two situations above, use inline/dynamic SQL in the code, but it needs to be a parametrized SQL. (classic ASP doesn’t have this but ASP.NET)

References

http://codebetter.com/blogs/eric.wise/archive/2006/05/24/145393.aspx

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

http://weblogs.asp.net/aaguiar/archive/2006/06/22/Stored-Procs-vs-Dynamic-SQL.aspx

http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx

http://www.theserverside.net/news/thread.tss?thread_id=31953

http://www.codinghorror.com/blog/archives/000292.html

http://www.codinghorror.com/blog/archives/000117.html

Tags: Adhoc Inline Dynamic, SQL query, Stored Procedures, vs verses