InLine Query vs. Stored Procedure

Posted on 2008-10-16
Last Modified: 2012-05-05
I received a post from an anonymous guy. According to him:
"........... Stored Procedures are now-a-days not a choice for enterprise applications. Post Sql Server 7.5, there is no difference left between stored procedures and inline query. Since that version, inline sql with named parameters is compiled into an execution plan once and stored for further access just like stored procs.

Stored procedures should not be used for most enterprise applications whether they be desktop or web based. A domain model that encapsulates the business rules is a much better solution. refactoring C# code is much easier than refactoring hundreds or thousands of stored procs.

Inline sql is not executed on the client. It is sent to the database, where it is compiled into an execution plan, stored, then executed. It is no different than using a stored procedure.

All data validation should happen in the application, simple validation where the user enters the data, and more complex business rule validation in the domain classes................."

I have a different view. I think, the tasks that an RDBMS is made to perform better, must be left to the RDBMS itself. Why data validation must happen via application, when it can be managed by an RDBMS? Why write additional lines of code for validating data through application?

As far as as the use of Stored Procedures are concerned, I am still seeing extensive use of them in enterprise applications and they are still a matter of choice.

Please clarify my doubts. I also want to know whether inLine execution, as mentioned in the excerpt above, is prone to sql-injection or not?
Question by:rpkhare
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 60

Accepted Solution

chapmandew earned 250 total points
ID: 22734610
inline sql is absolutely prone to sql-injection.  you have to validate user input if you're going to do it inline.  As far as performance, they are really one in the same.  With never versions of sql server, inline should perform just as well as procs.  The advantage of procs is the encapsulation of passing the parameters, as well as not giving any user explicit permissions to the underlying tables.  If they need to update data, you create a procedure that updates specific data and give them permission.  make sense?
LVL 20

Expert Comment

ID: 22735253
>>Why data validation must happen via application, when it can be managed by an RDBMS? Why write additional lines of code for validating data through application?

Sounds like your guy is not exactly fond of modern databases. Someone would have to be off his rocker not to have at least *some* (between "none" and "all") level of validation done within the server, not in the application.

LVL 13

Expert Comment

ID: 22735429
If you use inline parameters then it is NOT liable to sql injection.
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

LVL 60

Expert Comment

ID: 22735463
True...but you still have to give explicit permissions to the underlying tables.  IMO, the real advantage of sprocs comes from the administration perspective.
LVL 13

Assisted Solution

crazyman earned 250 total points
ID: 22735649
We tend to use sprocs 99.9% of the time, sometimes when the need arises to create dynamic sql depending on user input or some other unknown variable we use inline sql with parameters.
This argument has been done to death and to be honest i think both options have merits, there is really no right or wrong answer, it all depends on what your requirements are, where your strengths lie and what is more important to you.

For instance you need to pull all rows from a small two column lookup table with no where clause, is this really a good candidate for a sproc?
LVL 60

Expert Comment

ID: 22735688
I think so, but that is just my opinion.  :)

LVL 13

Expert Comment

ID: 22735870
i would probably go sproc as well, mainly for consistantcy and admin reasons, but i wouldnt slate anyone that didn't.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question