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
  • 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.
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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 shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now