In a recent article, Doug Reilly makes a fairly well reasoned case for the use of stored procedures in lieu of ad-hoc SQL:
Given the fact that most databases used today are just big boxes filled with crap, I must grudgingly agree with most of your observations. I do however, wish there were decently designed databases that enforced relationships, encapsulated rules and required domains to be specified.
Nice recommendations. I am in the process of doing some sql âstuffâ for my thesis and I was contemplating using stored procs to improve performance. The current method of using XML Web Services with native sql is too slow for the massive amounts of data I need to process. So I thought Iâd look into SQL Server 2005 which can âapparentlyâ integrate Web Services. How much of this is true is still unknown to me. But thats an interesting approach donât you think?
I think one of the great things about stored procedures that shouldnât be readily discounted is the fact that you can limit a user account to only execute stored procedures and furthermore you can specify exactly which stored procedures the account can execute.
The alternative is to give the account select, insert, delete, and update permissions. And that means that whoever can get access to the SQL user can do whatever he wants instead of whatever you want him to do.
Iâm not a DBA so there may be some way to lock down user accounts that Iâm not aware of, but this feature trumps all of the supposed performance and code cleanliness arguments. Theyâre gravy in my book.
At the job where I learned SQL, we used stored procedures and triggers a lot. The system included a database core with a suite of applications - some web-based, some unix, some (old) Mac - around it. The apps were written in C, C++, Perl, SmallTalk, and probably a few other languages Iâve forgotten. AppleScript, too.
The idea there was to embed (much of) the business logic into the database, so that all the applications, wherever hosted, would follow the same logic; also to avoid duplication.
People talked about efficiency, but I never saw any evidence of it, either way.
A final advantage of this approach was the deployment issue. Some of our apps needed to be distributed to users, or users needed to be forced off the system to update an app. When the logic of a stored procedure needed to change, we could just change it, live, and everyone would immediately see the benefit. Or bug.
I was comfortable with this system and these justifications for stored procedures, and I feel that it worked pretty well for us. But as you say, every project is different; I wouldnât expect this approach to apply to all cases.
I was comfortable with this system and these justifications for stored procedures
Sure; back then a web service wasnât an option. But based on your description itâd be a good candidate for that approach today, particularly with all the different platforms.
I still maintain that stored procedures are a terribly limited place to build an API, though.
Web Services with native sql is too slow for the massive amounts of data I need to process
Thatâs understandable. Are you returning lots of rows or just processing a lot of rows to generate smaller result sets?
So I thought Iâd look into SQL Server 2005 which can âapparentlyâ integrate Web Services
Interesting, I wasnât aware of that; the feature is called âHTTP Endpointsâ
âI think one of the great things about stored procedures that shouldnât be readily discounted is the fact that you can limit a user account to only execute stored procedures and furthermore you can specify exactly which stored procedures the account can execute.â
Maybe I am unique but I have not written a program/web page/what ever in the past 10 year where the database built in logins were used for end user authentication and authorization. I have long switched over to the application controlling access and usage and the user never seening the database access accounts or passwords(when needed).
From an end user(exclude dba and dev tools) stand point the only thing I have even seen in that time period are report type tools that allow you to build custom reports and I would not like to have to make the users use stored procedures in thoses tools to access there data ; and thoses accounts are usally limited to read-only.
The problem with using stored procedures in the way you mention is that you donât have the fine granularity that you get with using views and granting select/update/delete to tables or columns as/if needed. With most implementation of SP once you get to the SP you can enter any information as the parameter and if you are doing all CRUD work with SP you have to have SPs for all tables which would allow you to do almost anything you could do with SQL. No real additional security, and administrative wise it is alot easer to handle roles linked to views and tables then all the extra baggage the doing all CRUD work in SP brings.
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.
I agree with you Bill, but I think I understand what Will is getting at. Iâve worked on many apps which had both SP and raw SQL interfaces. This is usually necessary because the demands for ad-hoc reporting are too broad and general to be encapsulated in any set of stored procedures.
So what you end up with is, in many ways, the worst of both worlds. Raw SQL access to the underlying tables for the developer(s) writing the reporting code, and âabstractedâ SP-only access for the developer(s) writing the application itself.
Hmmm
I agree that wrinting âMindlessâ SP is NO fun. But i also see the advantages of them.
The only problem is writing them. Writing âsimpleâ SP is repetative, mindless and boring work. If anyone objects⌠Thats your problem. I rater write some software to create those SP from the Metadate that the SQL Server offers me. This is easy once you got the hang of it and it will allow you to create most âbasicâ SP you need. The SQL-Server knows all the information you need to know to start pumping out those SPs And Automatic generations of SPs like Get_Custumer_Orders isnt that hard. The key here is that you need to properly design the DB. This can save you tons of work later.
I am a lazy Bastard and i see that as a good trait 
Heiko,
I agree that code generation is a good way to go here. But items #2 and #3 on your list arenât really advantages:
#2 it is possible to write procs that are vulnerable to insertion. If you use parameterized SQL, which you should ALWAYS be doing, youâre basically protected from injection attacks. So itâs kind of a wash.
#3 parameterized SQL is hashed and compared to a precompiled execution plan after the server has seen that query more than once. All subsequent uses are precompiled. The only disadvantage is slightly more data sent over the wire in the initial request⌠eg, âSELECT * ⌠[500 more chars]â versus âMyStoredProc(param1, param2)â
As for #1 I personally feel procs are a bad place to build access control, but it is a valid use.
I am a lazy Bastard and i see that as a good trait
LOL, yes, lazy is good. If itâs the right kind of lazyâŚ
I just took a second to read the Article⌠(So far i only read the Blog) and i must say I didnt know that SQL is also storing an Execution plan.
But i like working with a robust DB. That means a DB that isnt just a loose collection of tables but has some well defined contrains. And thse informations âbelongâ into the DB for me. I hate doing work twice so you wont find me coding the same contrains on all levels of my application
In number #2 I quoted âautomaticâ since those SP are also accessed by my (generated) DAL. So far i find it easier to generate then to type by hand. (I really enjoy generating code)
Anyway i like the Article you posted And beeing lazy can be a lot of work if its done properly
Iâd like to add several clear cut benefits to using stored procs instead of dynamic SQL. I think my points have not been discussed much so far.
The problem of complexity has been solved many years ago. Put simply, Divide and conquer. By dividing any complex (coding) task, it becomes far easier to solve it.
This means that Stored Procs section off your application so that it is easier to test and easier to design. Stored procs provide small logic blocks that can be tested independently of compiled code. This separation of executable code from Database code makes debugging FAR faster and easier.
Just like graphical artists can improve a page because we now separate off the HTML from our actual code, Stored procs allow developers to work on the data side of their apps separately from their executable code. Mixing things, then, is in general, a limiting and bad practice.
If a program generates dynamic or concatenated SQL, you donât really know exactly what SQL is running unless you step through the exact code with the exact values. Sectioning off part of the work into a stored proc keeps things much simpler. It is possible to use the MS SQL profiler to debug dyncamic SQL but it requires DBA level permissions which are nearly always denied to developers.
Another benefit to Stored procs is that SQL is simply terribly messy to embed directly into code pages. At any of the large corporations Iâve worked at, a single table frequently had over 30 columns; some had 200 columns. Often, insert statements required values from other supporting tables so the SQL statements would be endlessly long. To embed that quantity of logic and SQL into a code page is a real mess.
And, update statements or select statements can take 4 or 5 times as many lines as insert statements.
Itâs far easier and more maintainable to view and work with SQL separately instead of having it mixed with code.
Just as in the âoldâ days when ASP was mixed with javascript and HTML, itâs FAR easier and cleaner to avoid mixing languages inside a code page. As most of us know, In the âoldâ days, ASP mixed with javascript, mixed with XML, mixed with html created a true mess of spaghetti code. The same principle holds on mixing SQL with code.
Also, using stored procs allows different applications and developers to use common database functions. SPâs are a simple way to encapsulate common sections of business logic that are easily reusable despite language and platform differences.
Iâm not at all convinced that stored procs are any faster for simple insert or select statements though. But in terms of keeping coding more organized, in terms of debugging, in terms of building larger more sophisticated applications, Stored procs are practically essential. If you canât agree with these points, ask most any DBA what he thinks. DBAâs work daily with VERY large applications that use heavy stored procs.
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. Iâve worked at companies where the lack of stored procs contributed to very messy and confusing code.
When executable code and database code are mixed together so tightly, it encourages a mixture of business logic and database logic.
From there itâs only a small step to add in some GUI logic and weâre right back in the days of spaghetti coding with a complete lack of separated layers.
A fundamental principle of coding is to use one or two word function names to execute more complex tasks. In the same way, stored procs are like functions that hide more complex tasks. Hiding complexity and dividing up complexity keeps coding simpler.
SQL is a separate language with itâs own complexities that are best handled in itâs own environment.
In summary, stored procs keep coding and debugging simpler by dividing the work up into layers.
Mark Tennenhouse
In response to Markâs comment about business/data separation, the correct recommendation for that problem is to separate your code into business classes and data access classes. That means that no business objects (whether persistent or not) should do any data access, and that no data access objects should implement any business logic. This guarantees business/data separation, and simplifies any subsequent modification. Any parameterized queries in your code are found in the data access layer implementation objects. There are many solutions for the lack of clear data/business separation in .NET, but although stored procedures are useful in many ways, they are not a good solution to that particular problem.
A step in the right direction:
a href="http://www.ejbsig.de/docs/PetShopArchitecture.html"http://www.ejbsig.de/docs/PetShopArchitecture.html/a
The main problems I have seen in .NET web app design and dev in recent years is that ex-ASP and other such people are building n-tier object orientated apps with a relational perspective. They start by getting some loose set of functions that they think the client wants and call them ârequirementsâ. Then the next thing they do is do the data model and database (for chrissakes). This makes the whole application inherently ârelationalâ, and when the OO programmers come to implement any business logic in C# or VB.NET the object-relational mapping exercise tends to be over complicated, and naturally a lot of logic and behaviour tends to fall into the database and outside of the code. For me, stored procedures in this kind of environment are a danger sign full stop, and one way of getting these people to turn their methodology around, and make the object model prior to the database model (and hence logic naturally falls into the object model and not the db) is simply to ban the use of stored procedures for anything other than report type queries and wotnot. Otherwise its a case of give 'em an inch and they take a mile. It starts of as one simple CRUD sp, and suddenly some db guru has converted into a t-sql neural network to detect market fraud.
Really the dynamic sql vs stored proc debate is good for at least one thing which is to open these peopleâs eyes to the possibilities
What is your take on Microsoft Data Application Blocks?
The way I see it is that, if implementing something is going to require a whole pile of ad-hoc SQL statements to implement a single business operation then itâs likely that itâs better to implement it as a sproc. The network latency will kill you.
However, in that case, you maybe should look at the database design as well. Sometimes thatâs the reason why it takes a pile of statements to do something.
In my case itâs trees and I work with people whom I shudder at the thought of explaining the nested set model for trees.
stored procedures are not bad. saying theyâre bad is like saying that guns are bad. stored procedures donât create bad code, people create bad code. Iâm fine with you not using them. but please stop spreading rumors about how âalmost all stored procedures are badâ
In in-house enterprise applications where one has complete control over the app and database, I âgenerallyâ opt for stored procedures.
In commercial retail boxed solutions which need to sit on any type of DB, I âgenerallyâ opt for parameterized queries using ANSI SQL. These parameterized queries can be further abstracted out into satellite assemblies, or other type of file (assuming some type of protection/security is implemented), so they can be replaced without recompiling the application, or messing with the database. When it comes to testing the SQL I usually hope for a DB programmer/DBA to give the SQL their stamp of approval, ensuring that even the parameterized queries are scripted uniformly.
Even if the commercial solution is tightly bound to SQL Server as its DB platform, I shy away from SPâs because of prying eyes. I could solve that by compiling the sp using âWITH ENCRYPTIONâ, but you better not loose those scripts!
Of course, these are generalizations, and I have to evaluate the options on a case-by-case basis.