Stored Queries?

Is it considered crazy to store common SQL queries for my web app in a database for use in execution? Or is that common practice? Or is it im开发者_开发知识库possible?

My thinking is, this way, I avoid hard-coding SQL into my application files, and add another level of abstraction.

Is this crazy? Is this what a stored procedure is? Or is that something else?

---

EDIT: The below answers are useful as a background for 'stored procedures', but didn't answer my core question: Is a 'stored procedure' just when I have a database table that contains queries that can be called? ie, something like this

INDEX | NAME          | QUERY
1     | show_names    | "SELECT names.first, names.last FROM names;"
2     | show_5_cities | "SELECT cities.city FROM cities LIMIT 0,5;"
etc.

Or is there a more complicated mechanism that encompasses the concept of stored procedures? Is my example an actual example of something people do?

---

Along with MUG4N's great reasons on why to use stored procedures, here are three more:

Security

You can grant access to your application to execute stored procedures while denying direct table access.

Think defense in depth. If your app is cracked, then they will be limited to executing ONLY the procedures you have defined. This means things like 'drop table' would be explicitly disallowed, unless, of course, you have a procedure to do that.

Conversely, if your app is cracked and you allow the app to have full access to your sql server, then one of two things will happen. Either your data disappears and/or the cracker easily get's a copy.

---

Unit Testing. It's much easier to unit test your queries if you can hit them directly without having to go through the application itself.

---

In Flight Changes: If you need to modify a query AFTER you have published your site, it's much easier to just make a proc change than redeploy code that may have undergone other changes since the last deployment. For example, let's say you have a page that isn't performing all that well. After evaluation, you determine that just changing the joins on a query will fix this. Modify the proc and go.

---

Well in my opinion you should definitly use stored procedures. And this is common practice!

Here are just two advantages of using stored procedures:

They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent. If your setup involves different clients, different programming languages - the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.

They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.

---

The idea certainly has its appeal -- but the problems is, they are nearly impossible to scale.. I have never seen a scalable solution to maintaining stored procs (especially in MySQL) that has not made me shutter.

Since it seems you're heading the PHP/MySQL route, I'll give a few examples of my experience with stored procs in MySQL:

• They are generally far less readable and far more difficult to write than PHP.
• They make debugging a nightmare
  • Trying to figure out why changing a value in table_1 triggers a change in table_2 (if you're even lucky enough to recognize that this happens) is much more difficult to determine by looking through dozens of stored procedures than it is to, say, look in the Model that handles changes to table_1.
• To my knowledge there is no standardized & automated way to integrate stored procs / triggers / etc into any revision control system

---

A stored procedure is just one or more SQL statements that are "pre-compiled" and live inside the database. You call them to return one or more rows of data, or to update, insert, or delete data.

If you tell us what web framework and database you are using, we can give you actual examples of how to call a stored procedure, or at least point you to an article or two to get you going.

You could also consider using an ORM framework, such as Hibernate. This will allow you to get away from dealing with SQL code altogether. I am a .Net developer, so I'm not sure what is available to you on the PHP/MySQL platform, but I am sure there is a lot out there to choose from.

---

You should think about it, when developing a commercial grade tiered application there is always people behind the database making it secure and reliable, other people are behind the application logic and other people behind the web code, so you can get the best of all working together.

Once the application has been designed, everyone start making their implementations, the db people give to the others some kind of API to use hiding the SQL, the developers won't have to think about it and focus on their code, i had worked as db developer and used some COM techniques to overcome the expansion and modification of the application logic or reuse, the database in these kind of products is too important to leave it in the wild so security it's a really serious issue.

But in most cases, web applications are made by web developers and they tend to have no design time, making big changes on the near time so they don't use stored procedures, also they don't even secure execution or try to leave security to the application leaving the database unprotected and prone to attacks.

If you're doing everything and changing your product too often you should avoid them since it will be double work and most of the times will be useless, once you stabilize your logic then you could start migrating your heavier queries to stored procedures.