SQL Server Dynamic SQL

The use of dynamic SQL is unfortunately one of those necessary evils that plagues developers and DBAs alike.

Now I know that the use of the word "necessary" will irk some people but after developing BI systems for about 9 years I can assure you that it is. Sure, there are alternatives in a lot of cases, which I have learnt over time, but even so there are occasions where even those don't help. Things like synonyms can help as can linked views but there are still times when dynamic SQL is required.

Either way, I'm not aiming this post to discuss the merits or demerits of dynamic SQL but rather at giving some airtime to a friendly yet oft unused SQL Server function. First off, hands up me 'cos I very rarely use this function too though I do have a mitigating factor.

Essentially we all know (or at least we should) that it is good practice to use qualified object names and that one should delimit object names to prevent runtime errors with bad practice object names. Some column and table names I've seen in my time include names with spaces, dollar signs, quotation marks etc. all of which I regard as evil. Let's not also forget the use of reserved words as object names which cannot always be easily avoided.

So here's my mitigating factor. I have been lucky enough to be the technical lead on most projects where I am a bit of a hardcase on naming conventions so I have control over what objects are called and created. For those that aren't so lucky...

Enter from stage left: The QUOTENAME function.

Look it up in BOL, it's great. That little function will place the correct delimiters around a given string to present a well formed object name. This means you don't have to specify square brackets, or whatever delimiter you are using in your dynamic SQL. Obviously though, you will have that function scattered throughout your code unless you declare object names up front using the function, placing the results into parameters and then using those parameters in the string concatenation.

Whether or not you use the function is by the by I guess, the main thing is to always delimit your object names. Still, it's good to know that the functionality exists if you ever need it.

- Posted using BlogPress from my iPhone