There are many times when I have found it necessary to perform simular operations to large numbers of objects. Applying privileges, performing calculations, creating views, recovering databases the list goes on and on. Over the years many features have emerged to assist with this process. Each new approach comes with its own set of opportunities. Many emerging tools are industrial strengthed and prove to be the best practice for the tasks that they are designed to perform. Some of these types of tools are Wizards and Templates.
Many times I find an additional need to perform very specific tasks that are unique to the problem at hand. Maybe there is a tool that does most of the task but requires a few key strokes or additional lines of code in order to finish the job. That is great until the number of repetitions gets bigger. It may only take a few seconds to add a few key strokes but to add a set of key strokes to fifty scripts starts to become tedious. Often times the number may become hundreds of scripts. Then you do it again because something else changed.
Any of these scenarios might be described as performing a set of operations to or for a set of objects. The set of operations to be performed by also be comprised of other subsets of operations for each object and any of the operations to be performed may be conditional.
You have probably solved these problems many times.
As soon as I realized that the word "set" was found in my perspective of the matter at hand my mind went to SQL might provide a consistent solution. Once I realized that, I was able to generate a lot of work fairly quickly by using SQL. The problem then emerged that I did too much with SQL, or at least too much for me. The more that I generated, the more complex my generation statements became. Sometimes my productivity was lost because of the errors that I introduced and the difficulty in discovering the cause.
It occurred to me that the bigger and more complex my generations were, the more error prone they became and the more the return on my investment of effort diminished. I had already learned this phenomenon regarding many other disciplines but I ignored this problem when generating SQL statements.
It eventually became apparent that I could consistently generate small pieces of code that were easy to debug and put them together into a larger solution without losing the simplicity of debugging the small bits of code. All I had to do was to assign the individual bits of code that I wanted to generate a key. Each bit of information is for a particular purpose and that can be uniquely identified. In addition to the unique purpose, each bit of text may need to be in a particular position with respect to other pieces of text. If it may require a certain position then it would hold true that if I simply say that it "must" require placement in a particular position then the rule will still work. The key would have to uniquely identify the bit of parsing for that statement and express the position as a whole as well as the position in each part.
These were the basic problems and solutions that I had encountered. All I had to do was assign appropriate keys and have the ability to navigate the code generation code to the key that identified the source of the problem.
I will give an example of a problem that was solved using this approach. At this time I won’t go into the details of the step-by-step authoring process but just state the problem and show the solution.
The need in this example is to develop standard update procedure for a given table.
Try it out and see what you think!
We will select the table HumanResources.Department in the AdventureWorks2008R2 database.