when to use sql and when to use stored procedure?

when to use sql and when to use stored procedure?

what is the difference between them? I just know the stored procedure is precomplied and stored in the database server, we can reuse it. but what about sql? for example:

开发者_高级运维select * from Employee

this sql should be compiled every time we send it to database? and what about other difference?

---

The question can be re-interpreted as "Where do you want to keep your business logic?"

Business logic is the core functionality for your system that defines how you want it to work.

You can choose to put your business logic in your DB (ie as stored procedures, stored functions, table constraints, etc) or in your application code (ie in software functions which then generate queries).

This is an architectural decision, but in general I would recommend only using SPs where it makes sense (eg for a query that is repeated often in numerous places in your code, or for specific performance in an area where you really need it and where using an SP would make a real difference).

Here's a link to an article which may help: http://c2.com/cgi/wiki?BusinessLogicInStoredProcedures