You mean, do you use stored procedures or client-side dynamic SQL?
Short answer, stored procedures.
Longer answer, it kind of depends. If you're working with an ORM product that creates good paramaterized SQL statements (and by good, I mean statements that will create a single query plan, not X number of plans like Hibernate does), then a well-structured paramaterized query is effectively the same thing as a stored procedure, so this is OK too. If you're just building SQL statements within the Java code and tossing them over the wall at the SQL Server instance, no, that's a problem for any number of reasons, not the least being a horrible thing called SQL Injection. There is a lot more subtlety & nuance that can be applied, but in general, this is the advice I give my own development teams.
answered May 12 '10 at 10:12 AM
Grant Fritchey ♦♦
I agree with everything Grant says, as usual. But I think a couple of those nuances he mentioned are worth elaborating on.
First, remember that ORM's are generally harder to optimize and troubleshoot than a stored procedure. This is not to say that ORMs are not great under certain circumstances, but that you should be very aware of the trade offs involved. There is some discussion of this at: http://ask.sqlservercentral.com/questions/1094/orms-from-a-dbas-perspective
Next, I do think there are some rare occassions where it makes sense to write code that directly sends SQL to the SQL Instance. I do this for instance for a few scripts where it is generating dynamic SQL based on sophisticated string parsing that is hard to achieve inside of SQL. But I do this rarely and only on programs that are meant to be run by the DBA team. These will break horribly and intentionally if run by someone without a high level of permissions. And again, while I do see some cases where it makes sense to encode SQL like that they are the rare exception.
As a general rule, I once again agree with Grant that stored procedures are the way to go most of the time and that ORMs are good in some situations. I would in particular very strongly advise avoiding encoded SQL on any program that is end user facing and especially if that program runs with higher permissions than the user has natively.
answered May 12 '10 at 11:31 AM