Why won't Dynamic SQL Die?
On a regular basis someone posts a question on the NG's about how apostrophes are screwing up their code and what can be done about it. So let me be blunt: DYNAMIC SQL NEEDS TO DIE. Period, end of story. For a long time, I was rather passive about this subject, but I think that's no longer appropriate. So, I'm going to use every venue I have at my disposal to beat this point into the ground. Why?
- Dynamic SQL is inefficient. Using Stored Procedures is obviously the 'best' solution, but I've heard a lot of folks tell me due to ridiculous corporate politics, they aren't allowed to use Stored Procs. This is so absurd it's like something out of Dilbert, but regrettably it's true. However, you can still construct Paramaterized queries without using procs. This will allow for cached execution plans which is often going to afford rather profound performance differences, particularly in large multiuser environments.
- Dyamic SQL is verbose and ugly. I'll let an example speak for itself. Compare these two statements which produce the same resultset.
“SELECT * FROM SomeTable WHERE SomeColumn = '“ & SomeValue & “', AND SomeotherValue = '“ & SomeOtherValue “', AND SomeFinalVaue = '“ & SomeFinalValue & “'“
VS.
“SELECT * FROM SomeTable Where SomeColumn = @SomeValue and SomeOtherValue = @SomeOtherValue AND SomeFinalValue = @SomeFinalValue“
cmd.Parameters.Add(“@SomeValue“, SqlDbType.Varchar, 50).Value = SomeValue
cmd.Parameters.Add(“@SomeOtherValue“, SqlDbType.Varchar, 50).Value = SomeOtherValue
cmd.Parameters.Add(“@SomeFinalValue“, SqlDbType.Varchar, 10).Value = SomeFinalValue
The parameterized version is much easier to construct (no string concatenation) and everything is clear and understandable. Compare that with the slop above it and the results speak for themselves.
3. Dyamic SQL Is Error Prone. There are many people with Irish surnames in this world like O'Brien for instance. That will cause your Dynamic SQL Statement to blow up. So, you can either use some String.Replace function to replace the single quotes or you can have it blow up on you. But, let's say you have 100 queries in your app. That's a whole lot of extra code but more importantly, you're probably going to forget to escape it at one point or another. And even if you are really anal and do this perfectly, another programmer not so familiar with the code will invariably forget it. And the best part is that in most instances, the bug won't show itself in early testing. It will probably come to your attention from a customer who can't do their job. So you fix it, recompile, and redistribute because there's no other way to fix the problem. This will not make you look like a competent professional
4. Dynamic SQL Is Dangerous. Too many hackers have figured out this vulnerability and they may well try to hit your database. Security by Hoping No One Pays attention to your app is a shameful policy and it could easily cost you your job, your company a lot of money and a lot more depending on what your app does.
Sure seems like a hell of a lot of downside just to keep a terrible habit. I've got a lot of Articles on KnowDotNet about this subject, and I'll have a lot more soon. Hopefully you can help spread the word and help send Dynamic SQL to an early grave!