The debate on Stored Procedures

An recent article I read discussed the issue of using stored procedures versus straight T-SQL statements. The author threw out some pros and cons but basically asked readers to give their thoughts. So here are mine:

I was brought up in the world of programming to always use stored procedures when accessing a database from a client program. The advantages were clear I was told:

1. The stored procedure was pre-compiled and therefore would run more quickly.
2. The store procedure provided better security through the use of the keyword GRANT.
3. The stored procedure protected against SQL injection attacks.
4. The stored procedure easily allowed me to pass parameters to my T-SQL.

I'm sure there are other advantages but those are all I remember at the present moment. Now, about three years later I am starting to question the "Stored Procedure Law". One of the biggest reasons is something I was little aware of in my earlier programming career called "Configuration Management". You have to get your stored procedures promoted from the development environment to production to insure that your code will work when it calls them. However, if you just write sure simple T-SQL in your code, you do not have to worry about this issue. This can be a huge advantage when the PM comes to you and says "Why is this not working?!!?".

Plus, let me give countering views to the four points I listed above in favor of stored procedures:

1. I am not so sure actually that the store procedure is precompiled. Possibly someone can point me to a link that clears this this issue up for me. I have heard arguments both for and against when it comes to SQL Server. Regardless, when you are running a simple select statement the time that it would save is inconsequential to the performance of your overall application. Hence, this is a moot point.
2. This is not true when .Net security is used correctly, especially when the right user permissions for the process running the application is in concert with Windows Integrated Authentication. Also SQL Server 2005 adds extended functionality to schemas, which can restrict the connecting client application to only access certain tables and perform certain functions.
3. This point seems deceivingly true. But when looked at more thoroughly, if a hacker is able to exploit a poorly written T-SQL statement in your code they will be able to equally do so even if it is contained in a stored procedure. The answer here is to validate all user input and to carefully write all T-SQL statements.
4. This point is true at first but again becomes moot when you learn how to pass parameters to a T-SQL statement in your code.

Well, that is my present opinion on the matter, but I am sure that it will change as time passes. Thanks for reading (my audience of one ;).

Eclipse

Today I did some programming in Java with the Eclipse IDE. I've been using Eclipse for about two months now, and I am really impressed. Having come from the Microsoft world and using Visual Studio .Net, I must say that my expectations were rather high. The learning curve for eclipse was rather steep, but I would expect that of any world-class IDE. I know that many C# developers out there are probably incredulous of what I'm saying, but I challenge them to give it a wholehearted try.

I used the book "Eclipse Distilled" to learn how to use eclipse, and I highly recommend it. The book was neither too short nor too long. It used a demonstration project as a learning guide, which helped immensely. Also after reading the introductory chapters you could choose from which chapters you would like to read a la carte.

Eclipse has all of the features that Visual Studio .Net has and then some. Plus, the developers are constantly adding new ones as their need becomes apparent. Visual Studio .Net has long release cycles (usually one to two years) that are at the sole discretion of Microsoft. Eclipse is also a platform, in that plug-ins can be developed for just about anything. Plus, you can use Eclipse as the foundation for your own application, much like a browser such as Firefox provides a GUI foundation for client/server applications. After going back to Visual Studio.net, I found myself saying many times: "Why doesn't Visual Studio have that? Why doesn't Visual Studio have this like Eclipse did?"

Finally, Eclipse also help me to see how customizable IDE's really are. I know it's rather simple, but being able to map Ctrl-L to do the same thing in two different IDE's helps my productivity immensely. Before eclipse, I usually just took Microsoft's defaults to be the one and only way. After Eclipse, I customize my shortcuts, perspectives, toolbars, etc. I feel I have been freed from one of Microsoft's shackles.

Dragon Naturally Speaking

okay. I have recently bought Dragon NaturallySpeaking 9 because I want to have a more active blog. Seeing as I haven't even posted anything since July, I believe it's time to enter the new frontier and just give it a whirl. So for now on and into the perceivable future I will be using Dragon NaturallySpeaking 9 to produce my blog. Hopefully this'll take care of my carpal tunnel syndrome (just joking). Also hopefully it will not be a mindless rambling of nothing that no one ever reads which I suspect most blogs are. Be that as it may, I have to start somewhere and this is the first step. Hopefully no one ever reads this post and the next post and probably the post after that, but eventually I will get to a point where Dragon NaturallySpeaking 9 and I become one and we use the force together and we'd learn all the jet 8-track tricks. That's interesting when I say Jedi it says jet 8-track :) While I still have to work on this, it's off to the new frontier and this is a new post. I will see you tomorrow hopefully, if not sooner. Good Bye.

ArcGIS Server 9.1 and ASP .Net

I have been taking classes to learn ArcGIS from ESRI. It is a very interesting and exciting technology, but is unfortunately stagnant due to ESRI's overwhelmingly large market share in the GIS software field. It is analogous to Microsoft's (MS) dominant position in the 90's that introduced problems in Windows 95 and Win 98. Since Win 95/98 had to be backwards compatible, MS was constrained to what they could do. Win 95/98 was infamous for crashing and presenting the blue screen of death. ArcGIS 9.1 Desktop suffers from the same problems. It crashes a lot, is arduous to install, and supports way too many anachronistic GIS file types. I say all of this because it is the root of the problem with ArcGIS 9.1 Server.

ArcGIS 9.1 Server is built on top of ArcObjects and ArcObjects are built with COM objects. This means .Net developers must interact with COM objects in their ArcGIS 9.1 Server applications. This, of course, introduces the dreaded InterOp assembly and leads to very messy coding with many hacks. While unfortunate, ESRI has no real incentive to upgrade ArcObjects to .Net because there is no one else to turn to for GIS web technology. Either you play the ESRI game or you don't play at all.