when to use sql and when to use stored procedure?

Question

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?

Possible duplicate of: http://stackoverflow.com/questions/2934634/ad-hoc-queries-vs-stored-procedures-vs-dynamic-sql — Jimmy Sawczuk, May 02 '11 at 12:25
I wouldn't use SP's for simple queries; I would ALWAYS use SP's for INSERT/UPDATE/DELETE operations — IMHO, May 05 '11 at 12:47

score 2 · Accepted Answer · answered May 11 '11 at 13:30

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

when to use sql and when to use stored procedure?

1 Answers1