Simple queries or Stored Procedures??

[David Mathew]

i am bit confused that in which situation one should use stored procedure and simple queries embedded in methods.
plz give ur opinions.

[Jeanne Boyarsky]

David,
A stored procedure is useful if it can eliminate network traffic. For example, if I want to collect values from different tables and insert them into a new table, a stored procedure is useful.
If I have a simple query that can be execute in one shot, like select name from table, a stored procedure does not provide additional benefits.

[steve souza]

If I have a simple query that can be execute in one shot, like select name from table, a stored procedure does not provide additional benefits.[/QB]

I like stored procs for the following reason:
1) They can be tested independently of java code, simply by using a standard SQL query tool
2) They can be maintained by non-java developers
3) They can be easily reused in other languages besides java.
4) They can easily be accessed from other Java applications
5) They hide the implementation and table structure from the java code (i.e. the java code only knows what they do, not how they do it)
6) They often offer better performance
The biggest reason people don't like them is that often the code in them is database dependent. But, in my experience most people don't stick to ANSI code in their sql anyway. I would hide the fact the fact that I am using stored procs (or straight sql for that matter) in an abstraction layer.
steve - http://www.fdsapi.com - the easiest way to render result sets as html and xml.

[Jeanne Boyarsky]

I like stored procs for the following reason:
Steve,
I agree with comments 1, 2 and 3. I think I assumed that a Java programmer was asking as we are on javaranch. (In our shop the java developers maintain the db stuff too.)
Comment 4 is an excellent point. It definitely makes reuse easier. Although, you could just jar up the data access layer and reuse that.
For comment 5, the implementation and table structure are still abstracted from most of the code. Ideally, they are in DAO type object. I can see some benefit to details being in the database though.
For comment 6, do stored procs offer better performance for very simple queries?
I was thinking about a query like: select name from list where id = ?. This is standard SQL. I can't imagine how a stored procedure would give you much benefit over a prepared statement. But I could be wrong.

[steve souza]

<<For comment 6, do stored procs offer better performance for very simple queries?>>
They probably don't offer much improved performance, but improved performance is often touted as a benefit of stored procs (at least in Sybase ASE). Of the 6 reasons I mentioned this is the one I care the least about.