could people please give their opinion on and give advantages and disadvantages on the use of sql queries directly within an application as opposed to creating procedures or functions within the database and using them.
closed as primarily opinion-based by RolandoMySQLDBA, Taryn♦, marc_s, Paul White♦, Mark Storey-Smith Mar 8 '14 at 5:01
Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.
A good inline SQL query, if made correctly using parameters is as
good as a stored procedure.
Raw SQL is more flexible to deal with.
Stored procedures have the benefits of security , but just raw performance isn't one of their major plus points.
Stored procedures are more maintainable.
Stored procedures are precompiled
The security and data layer separation benefits and increased ease of performance tuning provided by strictly using stored procs generally make them a better solution than ad-hoc queries regardless of any relative performance enhancement that may or may not come with the pre-compilation of the procs.
scalability and modularity
I'd personally like to utilize only Stored Procs for database access.
The only thing I see SQL doing that stored procs can't do (easily) is dynamically choosing the sort order. Other than that, it's about knowing how to code the stored procs the way you need.