InLine Query vs. Stored Procedure

Posted on 2008-10-16
Medium Priority
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 750 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.
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

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 750 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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