Today we have a guest editorial as Steve is on vacation.
I want to start with my definition of in-line SQL. I define it as any SQL statement, such as: selects, inserts, updates, or deletes, where they exist in text form in an application.
I think there are really two opinions on this issue. There people that hate in-line SQL and then those that think it is great. I would guess that some of you, that are reading this article, are DBA’s and wish there was no in-line SQL in the applications or databases you support. Others of you are on more of the developer spectrum might like in-line SQL in your application. Personally, I think that in-line SQL should only be used in the rarest of situations where something just cannot be easily done without it. Basically, I prefer and advocate using stored procedures only for application access to the database.
Clearly, using stored procedures only in an application can help with security. Both in the rights you need to give users and to protect from SQL injection attacks. Still, there is another reason I think stored procs are better for application data access. I have found that when there are bugs or changes that are needed, there are many cases where just the stored proc can be changed and released instead of a new build of the application. I can’t tell you how many times we have been able to quickly test and release a stored proc change verses trying to get a new build of an application out.
So why do people use in-line SQL? I know of a situation from a few years ago where the DBA was so slow to release changes to the database, the developers finally got tired of it and just moved everything to in-line SQL so they could better control releases and bug fixes. I am not sure if that is what the DBA wanted or not, but it was the result of the developers wanting more control to get releases / bug fixes out. I still don’t like in-line SQL, but I can see why someone could be driven to use it, just to try and get things done.
I think sometimes people use in-line SQL because it is less work at first. It is certainly more work to create the stored procedure, verses just drop a SQL statement into the application code. Still, I think it is less work only in the beginning. In the long run I believe stored procedures are less work and are easier to support over the life of an application.
Another good reason to use stored procedures only in an application is to be able to check dependencies. When all your database access exists only in stored procedures you can easily search those stored procs to find out how some change to the schema might affect existing SQL statements. When SQL statements are in-line they can be all over the place in code and not as easy to search accurately for them.
In the end you have to make the best decision you can for your situation and company. So how about you? What do you think about in-line SQL verses stored procs for database access in an application?
Ben Kubicek from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums
