Solved

InLine Query vs. Stored Procedure

Posted on 2008-10-16
7
2,311 Views
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?
0
Comment
Question by:rpkhare
  • 3
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
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?
0
 
LVL 20

Expert Comment

by:gatorvip
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.

0
 
LVL 13

Expert Comment

by:crazyman
ID: 22735429
If you use inline parameters then it is NOT liable to sql injection.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 60

Expert Comment

by:chapmandew
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.
0
 
LVL 13

Assisted Solution

by:crazyman
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?
0
 
LVL 60

Expert Comment

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

0
 
LVL 13

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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…
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 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