This is interesting. Many good arguments made. For us, we try to exercise some restraint when it comes to sp’s. Our ideal is to use parameterized SQL in a data access layer.
First, let me say that there are certainly reasons to use ad-hoc SQL in your applications. They provide a lot of runtime flexibility and in my mind are well suited to workgroup scale applications. In my view however, the story begins to change quite a bit when you move to an enterprise scale application or an application that relies on a strong security model.
If you look at a COM or other component interface, one of the primary values it provides is that the underlying logic can change but as long as we honor the interface contract, the caller doesn’t have to be aware of the changes.
This is a fundamental truth with stored procedures as well. Say you have a client application that issues a SQL Call. You’ve deployed it to 80 desktops. It worked fine for your first 40 users but as more users came online, it resulted in performance metrics that were problematic. You may get lucky and be able to solve the problem by adding an index, but what if the required change is really in the query itself? Maybe you are creating unanticipated locks and the query needs a NOLOCK hint. Then you have to deploy that change to the desktops. If the call were instead to a stored proc, you could optimize the SQL in that procedure and your client apps don’t know about it.
I work with a lot of .NET developers who know enough SQL to be very effective when building a custom application that serves a smaller audience. Most would fall down writing for big operations.
I’d also say that if you work on large development teams, the database layer is a natural dividing point for the work and allows the database interface to be reused by multiple clients effectively. Sure, you could code up the same select statement into the ten applications that the database serves, but this strikes me as a poor reuse model as well as an opportunity to introduce defects 10 times as opposed to once.
From a security perspective, I’d have to say that stored procedures in combination with views are almost certainly a more robust solution. As noted by another comment above, allowing ad-hoc SQL implies that the calling user context (whether integrated or standard security model is used) has access to the tables themselves. Such a model puts all the security control in the hands of the client when it probably ought to live in multiple layers. (Obviously, not all applications need to be particularly secure.) Restricting a user to the stored procedure interfaces not only abstracts the data, it protects the tables from a malicious user. If you want to then allow Ad-hoc queries, you expose the data you want exposed in a view, leaving sensitive data in the tables where the client can’t get it.
Re: "As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. "
As a further experiment, let’s look at a well-designed app that uses parameterized queries. Tossing out an example of some app that uses SQL coded directly into web pages is a straw man. Any decent app has not only confined SQL to the DAL (as others have mentioned), but organized the SQL into classes for ease of maintenance. For example, in our application, you add a field to the database and add it to the associated business object. That’s it. The DAL knows how to load, save, and delete. The business object knows how to enforce business rules. And recompiling isn’t a bad thing. You can catch a lot of errors that way! Hacking up a stored proc in a live database looks likes a recipe for disaster to me!
Re: “Egads, I wasn’t talking about having actual logins for the users of an app. I was referring to the logins that web apps run under.”
If all users run under the same database login (which is usual, in my experience) then you’ve lost whatever fine-grained control you may have gained by using SPs. Also, who in their right mind is even giving users access to the DB? You can’t touch our DB. You can only run the application. You might get a read-only database login with access to a few tables/views for ad-hoc reporting … if you ask real nice. 
Re: "Say you have a client application that issues a SQL Call. You’ve deployed it to 80 desktops. It worked fine for your first 40 users but as more users came online, it resulted in performance metrics that were problematic. You may get lucky and be able to solve the problem by adding an index, but what if the required change is really in the query itself? Maybe you are creating unanticipated locks and the query needs a NOLOCK hint. Then you have to deploy that change to the desktops. If the call were instead to a stored proc, you could optimize the SQL in that procedure and your client apps don’t know about it.
Sure, you could code up the same select statement into the ten applications that the database serves, but this strikes me as a poor reuse model as well as an opportunity to introduce defects 10 times as opposed to once."
Hmmm, I’m not buying the “do it to ease deployment issues” arguement. If you have 80 desktops, I hope you’ve already got a deployment methodology in place. I’m also tempted to throw in a snide remark about lack of load testing, but I do realize that bugs happen in the real world. However, I’m always cautious when people want to fix them in 5 minutes. How many “5 minute fixes” have turned into all-nighters when you broke more than you fixed?
As for 10 apps on one database, if they’re sharing the data they’re probably sharing some business logic as well. IMO, code-reuse goes in the application. You should be sharing business objects and a DAL, so you should still only have one change to make. Now versioning those changes among the 10 apps is another animal - one that SPs doesn’t solve either.
I would agree with others here that this is a great conversation. It is a discussion that I have had with numerous folks for over a decade. I will openly admit that I am a defector from the ad-hoc camp and have gone to the stored procedural dark side. There are numerous reasons and frankly they all stem from actual experience.
First, I would like to address the two point raised in your post. Intially I would address your comment “If your primary goal is abstraction, stored procedures are a terrible place to do that”. I simply cannot see any valid backing to that statement. You arguments correctly point out the danger of coupling but not abstraction. Consider abstraction where a framework is capable of being deployed upon say SQL Server or Oracle. Using commonly available objects in the .NET Framework we have demonstrated that the entire business layer can be 100% independent of any direct knowledge of the underlying database platform. That is a very critical form of abstraction. We can write a BL that can be deployed on Oracle or SQL Server without limiting the use of optimized SQL for each platform. That is a huge benefit.
Also, in your statement you mentioned that if the underlying data changes you have to change the stored procedure. Well, embedding ad hoc SQL in an application means the same plus you will have to recompile, build and deploy your application to address the change. The same is not necessarily true of the stored procedure approach precisely because it can help you decouple.
Also, I think there is an 800lb gorilla in the room with respect to this argument that nobody wants to discuss. Good developers don’t necessarily write good SQL. In fact, based on over a decade of experience with numbers major consulting firms, those that excel in writing OOP frequently suffer when trying to deal with how database platforms operate. By allowing the use of ad hoc SQL you distribute the SQL to many locations (within the code base) rather than leaving it in the hands of well trained database specialists. Further, the database specialist is limited about what they can do. This is why I have yet to ever encounter a DBA who prefers to have application developers gain direct access to tables.
Another point is that stored procedures simply are more secure and there is really no valid argument against this. Disallowing direct table access ensures that correctly written stored procedures can validate correct visibility to underlying data. Opening the database and exposing the tables lowers your security posture.
Lastly, there is no hope of SQL being generated and emitted from one machine can rival the performance of a well written stored procedure. I have gone through several benchmarking efforts and dynamic SQL never once bested the stored procedure approach. While the difference were rarely large differences the dynamic SQL approach never once came out on top.
Still, I love the debate. I think it will never end.
In many of these discussions, nobody seems to mention the application scope or application environment.
To me, the big difference in the stored procedure pro’s and con’s (and an addition to the ‘it depends’ answer) lies in the application scope and it’s environment.
If your scope is to support ONE DBMS, ONE customer with ONE application and ONE version, your approach could be one.
If your scope is to support SEVERAL DBMSs, SEVERAL customers with SEVERAL applications and highly versionized, your approach could be quite another. (not to talk about numerious combinations of the latter or the addition of different hosting/deployment environments).
If supporting several DBMSs, your SPs would have to be duplicated to hold each DBMS’s syntax. List your own consequences here.
If there exists business logic both in your code (.net, java, php or you-name-it) AND in your SPs, you have business logic in more that one place. List your own consequences here.
If there exists NO business logic in your SPs and instead they are only small clean ‘DAL-doors’, then their existence are questionable in the first place, why even have them?
Better performance is not only a database issue. A different modeling, normalization or programming approach alone could improve performance.
There are several answers to solving the SQL injection issue. Google it.
Code is for logic. Database is for saving.
Personal opinion: Because solutions should try to be future welcoming, foreseeing and able to solve yet-unknown-issues, then EVEN if my scope is ONE, ONE, ONE, ONE (see above), I still goes the ‘without stored procedure’ path.
If you’re selling software to other companies, you’re going to have to support more than one DBMS and leaning on stored procedures is absolutely a bad idea – no argument from me, there.
And yes, business logic in both the stored procedure and the higher-level code is going to be confusing. Don’t forget, though, that keeping business logic entirely in the database is also an option. If you’re careful with your database design, you can actually set up a pretty clean MVC separation with the database functioning as the model. It won’t scale as far as a app-server system, but it’s an awful lot simpler and should be good for a few hundred users.
No, it’s not a strawman at all. Whether you are embedding the code in an actual web page or in the code in a DAL the argument still holds true, that it’s much more difficult to maintain.
No, that’s not it…
Only after you tell them how with relation to the new column.
Talk about strawmen! You’re assuming that the SQL developer is “hacking up” a stored proc in a live database. The stored procedure would of course be tested before being put in a production environment, but it can be done without a major deployment.
Not at all. You can easily build in user-specific security in a stored procedure and it doesn’t have to be tied to the actual login being used.
Actually, he SPECIFICALLY said that he wasn’t talking about individual logins. Also, security isn’t always about users. Do you think that developers or other internal personnel are never an issue when it comes to security? I can’t count the number of times that I’ve seen a config file out on the web server with a SQL username and password sitting there in plain text for anyone with access to see. If security is tied to a Windows login then any developer with access to that login can log directly into the SQL Server and delete all of the rows of an entire table. “Whoops! I was just trying to fix a record!”
Just because you don’t buy it doesn’t mean it’s not true.
Sure, and how many of those methodologies require that users reboot their machines or exit the application or wait for an overnight (or worse, weekend) release? How many require a reboot of a web server or similar?
Yet another strawman (from someone who actually complained about strawmen arguments!). He didn’t say anything about trying to do a 5-minute fix.
One more thing about stored procedures. It would be nice if there was a stored procedure that could, with one shot, check the syntax of each and every stored procedure inside a database. The syntax should check for field validity. If any fieldname is changed in the scenario of thousands of stored procedure, not only are SP’s going to break, but automated maintainance processes that copy databases to backups will also fail because SQL verifies that all SP’s are correct before executing a DB copy or backup.
If the database schema changes then using an API is not really going to give you abstraction you still need to modify your API to take into count the new schema changes.
I totally agree with Mark Tennenhouse on October 11, 2005 07:55 PM
Divide and conquer. Let the technology do what it was designed to do. Mixing code platforms only creates more unnecessary complexity…
K.I.S.S.
Just because you can, doesn’t mean you should
Who wrote this crap!?
Obviously, I think we are missing the points of both stored procedures and raw SQL. Both have their own advantages.
As previously said raw SQL is a great solution for parameterized reporting and more complicated DB tasks(db backup, restores, table creation, database definition, etc.) Really, I would say anything which can not be automated easily, should be written in raw SQL. Since automation is difficult, more than likely so will control. This usually is a sign of inconsistency at one or more levels in the database, whether security, data, structure, or flow of control.
Stored procedures were created to place limitations on code/programs. Limits such as the number of recompiles, limitations on the executing code(if designed correctly you always know what an stored procedure is going to do, no exceptions), limits on the applied database security(keyword is applied not fundamental),and limits on the usage and redundancy(more consistency in your coding).
All in all the correct usage of these fall in the hand of us as IT Professionals. We must first know the advantages/reasons for which processes, such as stored procedures, were invented to control. Then, naturally the power to use each in the way intended is going to be a decision which ultimately falls into the hands of not the code or stored procedure, but into the hands of us as IT professionals.
The aim of an argument or discussion should not be victory, but progress.
- Joseph Joubert
G-CODE
Here’s a good reason to use stored procs when using VB (vs. C#):
VB’s god-awful string concatenation versus C#'s @‘string’ construct. VB makes me choose between nicely formatted sql code or LOTS of _
People talked about CRUD sps. Anti-SPers cite how boring and mindless they are to write. Pro-SPers counter with how you can use code generators to create them. However, one of the purposes, major purpose IMO, of SP is to hide database schema from application logic. So if SPs are used correctly, there should not be so many CRUD SPs at all, let alone 400+ or 600+ CRUD SPs for a 100 table database.
I believe both solutions have merit. I think that it is better not to decide One Way Or The Other Forever, but rather choose the best fitting solution for your expertise, team, environment, and problem space. What follows is my attempt at a check list for evaluating what approach is most likely to best fit your needs. Make a list with two columns, one for stored procedures, one for parameterized SQL, and add checks based on the list below. When you’re finished, the column with the most checks would be my recommendation based on your specific situation and skill set.
The Rules of Thumb in the Stored Procedures vs Parameterized SQL Debate:
-
If performance is a primary concern, and you must limit connections to the bare minimum possible and/or every byte is precious, add a check for stored procedures.
-
If application flexibility is far more important than performance, add a check for parameterized SQL.
-
If your team is highly compartmentalized, composed of smaller sub-teams of database experts and application experts add a check for stored procedures.
-
If your team is composed almost entirely of application developers who are much more comfortable with OOP code than the database, add a check for parameterized sql.
-
If your application must support many different database products or the specific database product being used will not be known, add a check for parameterized SQL.
-
If all your database products are known and are not likely to change, add a check for stored procedures.
-
If it is extremely important to limit user access to important data, or your database contains highly sensitive data, add a check for stored procedures.
-
If your application does not contain both important data and mundane data in the same table, or contains no highly sensitive data at all, add a check for parameterized sql.
-
If your app development team is highly experienced with data layers and exclusively controls the database connection tier in your application, and the database team is inexperienced or does not exist, add a check for parameterized sql.
-
If you are creating or support an application where other teams with far less experience will be creating their own data access tier or access tier elements and you have a skilled database expert or team of experts to act as SQL gatekeepers, add a check for stored procedures.
-
If you occasionally or commonly require a large string of SQL queries or JOINs to accomplish a needed application task in the database, or your tables often have large numbers of columns that must be dealt with in a query, add a check for stored procedures.
-
If your queries are limited almost exclusively to simple single line insert/update/delete/select statements against a single table, add a check for parameterized SQL.
-
If you’re working with an inexperienced team that does not understand SQL based attacks such as SQL injection and/or you cannot be sure that all members will write proper parameterized SQL, add a check for stored procedures.
-
If your team is composed of experienced app developers who have little experience with database design and optimization, add a check for parameterized SQL.
-
If your application environment combines both transactional and business intelligence operations, add a check for parameterized SQL.
-
If your application deals purely with a transactional database, add a check for stored procedures.
Hopefully you find this list helpful, perhaps in some small way it will help calm the debate and allow people to realize that there is no particular One Right Way in this debate, but two rather equal options.
From my experience using stored procedures is the only way to go. Performance aside most programmers simply don’t know anything about parameterized sql. It always ends up being sql string concat which is very very bad ummmkay. Stored procs allow you to change logic in the database with no re-dployment. You can change the proc and users are unaffected, assuming there are no bugs in there ;). Use ANSI SQL for your procs and you can port to any database you want. And I believe someone else said it but you can lock your DB down tighter this way. Just give a user execute to whatever procs you want and no one needs direct table access. Yes you might have a lot of procs because of this but I would rather clutter the DB then clutter the code with that stuff.
Just for those claiming that sprocs must be used at the ‘enterprise’ level; you are shamelessly wrong. The answer is that ‘it depends’ – development is fundamentally an engineering exercise balancing requirements and constraints. ‘Enterprise’ application is not a requirement; its a label with little value. The requirements that have value have already been enumerated above: performance, security, etc.
I personally lean more to the parameterized sql approach, because re-using business components at an application level has proved more useful in our domain over a number of products than trying to re-use sproces–throw in multiple RDBMS support and sprocs become insanely expensive to maintain. Echoing the author’s statement-- the vendor specific data layer is a terrible place to try begin abstractions. The performance argument is negligable at best in most circumstances.
My 2cents
Steve
Thanks for everyone who posted. As a MSSQL DBA I agree that ‘it depends’. I dont necessarily want to keep up with an sp for every simple single table SELECT query but relying on developers to write optimized sql is not feasable. This is NOT a critique of developers as I have written my share of web apps (pre .net), but it is VERY true that you can write a SQL statement in many different ways. It is not usually apparent which way is going to be the best until you get some performance metrics from the DBMS. Query performance depends on many factors including indexes on tables, Database Structure and such.
If you have a good DBA team, SPs can help everyone. It speeds up development time and allows performance metrics to be analyzed over time. When the DBA finds an underperforming SP it is easy to optimize, test changes and implement the change in the DBMS improving performance without any recompiles or developers performing application changes.
I’d rather debug an app that passed parameters to an SP than one that has tons of complex parameterized sql. I’ve seen queries executed against the database that caused blocking and needed optimization and it took the developers a lot of hours to find the location of the embedded sql in the source code. If it were in an SP it would have taken much less time.
Layering security is very cool. The .net framework has enhances security features but putting all of your security in one layer doesnt make much sense. Using the HTTP Endpoints to access SPs that have explicit permissions on the Stored Procedures instead of granting permissions directly to the tables adds another layer of security for the app and database against mistakes in coding (were only human) and gives the DBA the ability to enhance the performance of the database as well as securing it properly.
Is the now broken link to “Parameterized SQL” http://www.codinghorror.com/blog/archives/000275.html originally pointing to this article?
https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/
1 Like