My issue with inline sql is that some developers tend to gravitate to it so that they don’t have to rely on a DBA or, they simply don’t trust a DBA to get the job done or to care about the job that needs to be done.
The company I work for is an Oracle shop. We program in C# against an Oracle back-end. As with any Oracle shop, we have a rather large DBA group who focus their efforts each day on creating and maintaining PL/SQL Packages and Procedures. Their constant tuning and maintenance assures us that our applications are running as good as they can. However, some of our developers do not like to utilize this talent and instead like to completely mimic Oracle Procs within C# code using inline SQL.
My problem with this is that it is a dual effort. Upon release, the DBAs will continue fine tuning and modifying the existing procs that are still used ad-hoc and when schema changes are necessary they will make all the changes to their procs but our C# code will be left behind and thus we will have to play catchup.
I was really surprised when I started here to find that the main developer here was basically taking the “ideas” behind the currently used and maintained procs and then rewriting those within his C# and inline SQL. The outcome of his effort would be similar but not the same as the effort the DBAs did.
When asked why this was being done the answer was “I hate Oracle” and “inline procs ensure portability”. I ask “portability from what”. Oracle is very expensive and I really don’t see the company moving from Oracle to SQL Server any time soon so how does this factor in at all.
This has been irritating enough but one thing that really bothers me is that many of the pro-inline SQL guys here tend to break their SQL apart so that it cleanly displays on the coding screen and this is all well and good for printing but when you search the code looking for what code affects what Tables or Columns, it is very difficult because the Table and/or Column names could easily be broken apart within a string to go to a new line such as:
SELECT * FROM Customer_
Data WHERE Last_Contact
_Date = …
In the above case, the Table Name is “Customer_Data” but you will never find that in a search, and the Column being referenced is “Last_Contact_Date” but again, you will never find that in a search.
Another thing that irritates me is that inline SQL tends to lead developers to forget about development tiers… they will just toss inline SQL anywhere. For example, in a web site I maintain, all the inline SQL is in a resource file that is part of the web site… fine, its organized. But, the business tier has inline SQL listed as the values of Constants. Of course, I use the term “business” tier loosely because the web site does a lot of database interaction where it just handles it all on its own… clearly, N-tier development was forgotten here.
That leads me to the conclusion that while inline SQL may have its place, it does tend to lead to lazy, messy code. People tend to forget about layers or tiers of code and think in the moment… this needs to be done and I’m here so there is no better place.
Personally, I prefer all base data interaction to be through procs on the database server, I can live with minimal inline SQL but breaking down incredibly time consuming data processing that is working well in a native proc and creating a slew of inline SQL statements to replicate the proc is only going to lead to sloppy code, confusion, performance decreases and security risks.
Oh yeah, to make matters worse, the web app doesn’t do any validation to protect itself from SQL Injection and couple that with inline SQL and you have a nasty mix.
Just my $0.02