An interesting article by Douglas Reilly, To SP or not to SP in SQL Server, covers some interesting aspects while comparing those two approaches in several categories like security (SQL Injections), performance issue (pre-compile SP, does it help?), maintenance and cross platforms.

His final conclusion was “Its depends”.

As one who’s a strong advocate for stored procedures, I’m constantly looking for either justification or contradiction for either method, while observing the advantages for using either.

Even though the article’s arguments are strong and supported by empirical/technical evidence, I still believe that embedded SQL code is a bad idea. Having performed those tests and comparisons, Mr. Reilly has not addressed one important fact. I call it “specialty” or “area of expertise”.

The same as we decide to use different development languages for different purposes, where each language has its pros and cons for a certain field, we should apply the same decision mechanism when we ask ourselves “Where should we place our SQL code? Embedded or SP?”.

Now, because SQL server is designed for SQL/data/query processing, we should let it take the leading role in ALL data “manipulation” actions which are required by our application, while placing all our SQL code on that server. On the other hand, we should focus on making our code as efficient as possible when interacting with the database server. Doing so we will have a clear distinction in the roles of each of our system components (where database is one of those), while benefitting from the advantages given by each one of our system component.

My Conclusion: Since database servers specialize in query processing and data, they are the most appropriate location for such code, thus arguing for stored procedures over embedded SQL.