• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2591
  • Last Modified:

InLine Query vs. Stored Procedure

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?
0
rpkhare
Asked:
rpkhare
  • 3
  • 3
2 Solutions
 
chapmandewCommented:
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?
0
 
gatorvipCommented:
>>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.

0
 
crazymanCommented:
If you use inline parameters then it is NOT liable to sql injection.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
chapmandewCommented:
True...but you still have to give explicit permissions to the underlying tables.  IMO, the real advantage of sprocs comes from the administration perspective.
0
 
crazymanCommented:
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?
0
 
chapmandewCommented:
I think so, but that is just my opinion.  :)

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now