Developer Pills

# The Myth of Stored Procedures Preference

You've been kicked (a good thing) - Trackback from DotNetKicks.com

# Pages tagged "Diverse"

Pingback from  Pages tagged "Diverse"

# re: The Myth of Stored Procedures Preference

the only benefit I've ever seen for using stored procedures instead of dynamic sql is when more than one database server is involved. Using stored procedures and views can over come the burden of trying to mash together two separate databases within your application.

Within the stored procedure or view you can access all the linked servers that you want while still working from within a single database. This means that your application only needs to connect and use a single datasource.

Ever better is you are not limited in using linked servers, stored procedures and views to connect to other MS SQL databases, you can also connect to other database engine such as Oracle.

In the coldfusion project I'm working on right now I'm using linked servers and views to connect and mash database between MS SQL and Oracle. The view calls both database to mash data together. I also use views to insert data into the Oracle database, this could be accomplish using stored procedures, but I like using views instead.

# re: The Myth of Stored Procedures Preference

This is a very poorly written and constructed article.

# re: The Myth of Stored Procedures Preference

The paragraph about SQL injection is nonsense.

Executing a stored procedure is much like calling a simple SQL statement; it can be done in insecure ways (dumb string concatenation like in the example) or in secure ways (e.g. JDBC parameters), and both styles of SQL are equally exposed to SQL injection.

# Stored Procedures vs. Dynamic SQL - The never ending debate? « blog.jemm.net

Pingback from  Stored Procedures vs. Dynamic SQL - The never ending debate? « blog.jemm.net

# Stored Procedures vs. Dynamic SQL - The never ending debate? « blog.jemm.net

Pingback from  Stored Procedures vs. Dynamic SQL - The never ending debate? « blog.jemm.net

# re: The Myth of Stored Procedures Preference

In general if your write crappy code/have poor indexes/didn't set up SQL Server correctly it won't matter if you chose SPs over sql.  This is a real secondary thing.

I do think though that stored procedures help out a lot as units of deployment/organization as fixes/releases come into play.  Plus they abstract your code slightly as do views do.

# re: The Myth of Stored Procedures Preference

SP's do take longer to write, but it pays off in the long run if you end up with a big application -- it'll be easier to maintain.

If a SP isn't portable between various DBMS's, then your ad-hoc SQL probably isn't portable either.

SP's aren't a guarantee for perfect security, but they're still much better than ad-hoc SQL.  You have to give a user account much higher SQL permissions than you would if you were just using stored procedures.

Writing code like "EXEC sp_GetCustomerByEmail '" + txtEmailAddress.Text + "'"; isn't nearly as secure as using an SqlParameter -- the ideal method to call SP's is to call your SP and pass all its parameters as an arraylist.  There's much, much less potential for injection.

Also, with SP's, you don't have to recompile your ASP.NET application, you have better layer abstraction, and the best benefit -- you have a lot less transferred data.  Instead of transferring big, bulky ad-hoc SQL, you can transfer a stored procedure name and its parameters to the SQL server.

# re: The Myth of Stored Procedures Preference

>This is a very poorly written and constructed article.

I think it is rushed a bit, but all points it touches are good.

# re: The Myth of Stored Procedures Preference

This article makes the author appear ignorant.

If you were to blindly follow the authors advice you could end up in a world of hurt.

For example - the execution plan for a stored procedure is created and cached stored once. The execution plan for an ad-hoc query is stored for each variant of the query. So if the code has a query to get user details:

select * from user where email = 'bill@microsoft.com'

The execution plan is generated and cached every variant of the query (i.e every time a user logs in) Imaging if gmail used ad hoc queries in this manner.

I am not saying that the use of ad-hoc queries is bad, there are work arounds for this issue such as parameterised queries. I am saying that this article is poorly written and should be taken with a grain of salt.

# re: The Myth of Stored Procedures Preference

Agree with the points in the article for CRUD style operations.  

For intensive "batch" processing tasks such as collecting payments over 1 million customers, stored procedures significantly out perform dynamic SQL due to the latency between the application and the database.  Caching of query plans isn't the issue in this situation.

# re: The Myth of Stored Procedures Preference

While I do see your points there are a few things I see differently:

The biggest reason for SP:s in my point of view is ensuring data consistency. Limiting the surface area to the data.

In larger projects where many people/even multiple teams are involved it's simply stupid to put the responsibility for query writing to the

developers. Sooner or later they will forget that CREATED column should not be changed when UPDATING etc. The biggest asset lies in the data (not the app), and data should be protected for consistency by all possible means.

You say: "Minor change to the design require changing in both the SPs and the DAL code."   You see it as something negative, I see it as something positive.

I would rather have compilation error, or even app crashes than inconsistent data because developer (me maybe) forgot to change a SQL statement.

Kind of type safety...

With a good code generator (generating sp calls from meta data) it really doesn't have to be that much of a burden. Although I do really agree on the "Versioning sp code" point.    

Last I fully agree with Sean, "Not having to recompile your ASP.NET application" - saved me a couple of times,

"you have better layer abstraction", and "lot less transferred data"

# re: The Myth of Stored Procedures Preference

Adel sorry but i must disagree with you

hard coded sql statements in the DAL is worst thing i could think of for the following reasons

1- when ur sql code is large and what mean by large is more than 1000 line it's not a good idea to write it using an VS IDE

2- who said there is no versioning tools for sql? VSTS now offer support for SQL projects

3- it's always a good practice to encapsulate the database functionality in stored procedures, so when ever u need to change ur database design u can do it and change ur stored procedures without rebuilding and deploying the project, and i'm not talking here about the projects that need 5 min for deployment, i'm talking about projects that take days and sometimes requires staff to travel abroad to deploy it

4- i don't think VS is better than the sql studio from the productivity aspect to write sql statements

unless u r talking about statements like select * from products

5- "It's impossible to cover every single scenario" who said so? the same way you can make ur ad-hoc sql statements dynamic you can make stored procedures dynamic too, stored procedures have parameters for a reason

6- "string s = "EXEC sp_GetCustomerByEmail '" + txtEmailAddress.Text + "'";" i've never seen a developer stupid enough to do that :D

simply because if u use stored procedures with security in mind and u know that hard coded sql statements is BAD and vulnerable to sql injection, the u will never take the effort to implement a sql stored procedure then execute it from a hard coded sql statement

7- you can't use transactions in hard coded sql statements the same way like stored procedures, u will have to handle these transactions ur self from ur DAL code, which is something i don't recommend

8- if you implemented your transaction in ur DAL layer which is again something i don't recommend, if ur web application failed for any reason u won't be able to roll back specially if that failure reason was that the database is gone offline, which is again if u r dealing with money that would be the worst dream from the business aspect

9- stored procedures still maintain a higher performance for a simple reason "less round trips"

if u hard coded sql statements in ur DAL this will means lots and lots of round trips between you sql server and your application server when ever a user clicks a button on ur asp.net pages

# re: The Myth of Stored Procedures Preference

Ted,

Why do you think it is a poorly written article? I think it touches on valid points and is in-line with my experience. SPs are teh suck.

# re: The Myth of Stored Procedures Preference

Your security argument is very weak. The security benefits of stored procedures lie in not constructing a SQL string, in passing parameters to a procedure. SQL injection attacks are possible because people don't check their inputs when building a SQL statement. And in your argument for a weakness in stored procedures you do exactly that: dynamically build a SQL string AND don't check the inputs.

Proving that a secure technique can be used insecurely doesn't prove that it's insecure.

# re: The Myth of Stored Procedures Preference

They have helped me out a lot. I work with people that are SQL experts but I wouldn't want them in the source code recompiling. They can update, create and help me with a lot of things this way.

# re: The Myth of Stored Procedures Preference

Ok...let's not say SPs are great just because we have been drinking the kulade too long.

Here is why you should use them:

1) You wish to isolate users from the physical structure of your database. You need performance that views will not provide in dynamic SQL.

2) You have complicated code that is best broken down using temp tables for performance.

3) You want the pre-cache query plan only stored procedures provide effeciently. Dynamic SQL will cache a plan; but, it is dropped from Cache earlier when RAM is needed than SP Cache. It also takes more to determine if a Cached plan is useable because the query has to be compared to determine if it is the same as an already executed query. For simple CRUD queries, who cares. But if you have a database that really does something...

Here is why you should not use them:

1) To protect from SQL Injection. There are methods to make SQL Calls that do not require an SP yet provide the same protection for SQL Injection as an SP.

2) You wish to have your data access layer be More intellegent. You require a data access layer to be intimately aware of your data structure.

3) True transporability is required, and you have a middle tier that is capable of working with multiple SQL syntax.

There are many more bullets that could be added to either side of the arguement. The answer is, it depends on your situation...AS ALWAYS. This arguement is like saying everything should be written using a Factory Pattern.

# re: The Myth of Stored Procedures Preference

These are some of the worst arguments against the use of stored procedures I've ever read. Good grief.

# Vinny Carpenter’s blog » Daily del.icio.us for January 7th

Pingback from  Vinny Carpenter’s blog » Daily del.icio.us for January 7th

# re: The Myth of Stored Procedures Preference

This article is full of holes... the most obvious ones is the attack on SP security. If you implement SPs correctly, then this would not be a problem:

string s = "EXEC sp_GetCustomerByEmail '" + txtEmailAddress.Text + "'";

Even if txtEmailAddress.Text = "'; DROP DATABASE myDB", since the user executing that stored procedure only has rights to executing stored procedures and not random SQL... CERTAINLY not DROP rights, the Sql injection problem you mention does not exists, unless an incompetent developer creates it.

In answer to your "performance" attack, Yes.. SQL Server caches execution plans for random SQL, but not to the level that it does for SPs. SQL Server stores several execution plans for SPs and selects the best plan based on parameters, all tables and joins involved, as well as other costs calculations such as I/O and pages involved.

If you're using random SQL, you SQL statements must match perfectly in order for an execution plan to be reused.

# re: The Myth of Stored Procedures Preference

People seem to be ignorant of, or simply forget, that using dynamic sql does note equate to injecting values. Dynamic SQL can be, and should be, used with parameters just as you do stored procedures.

If you use parameterised sql then your cached plans are just as efficient as stored procedures.