0

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.

3

Raw SQL

  • 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

  • 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

Conclusion

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.

  • 1
    I would add that dynamically choosing the sort order could be handled in the presentation layer. Read all your data into a variable (DataTable or List<T> in C# is how i handle that) and then use the built in controls to sort the data. Unless there is a specific reason to return data in a specific order I find the presentation layer is faster at multiple sorts. – RubberChickenLeader Mar 7 '14 at 21:33
  • @Wind Raven ,Good point. – Up_One Mar 7 '14 at 21:34
  • Raw SQL / prepared statements in the application are written in your favorite programming language: that familiarity gives you increased speed of development and access to your normal debugging tools. Stored procedures are generally written in proprietary languages (PL/SQL, TSQL, etc.) so they are slower to develop and maintain (not always a bad thing, though). It can be difficult to track down performance problems because the SQL is "wrapped away" inside the procedure (Oracle Database is particularly bad for this). – Phill W. Oct 30 '18 at 11:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.