SQL Server Magazine

Your manager—or a client—approaches you with an assignment and says, "I want you to make this scope change. And by the way, you need to get the job done today—with documentation—because the other developers are waiting for this project." Then, when you begin work, you discover that completing the task entails altering tables, adding new foreign keys, and changing INSERT, UPDATE, DELETE, and SELECT procedures.

The scenario is all too common. SQL Server developers toil for companies that now crunch development cycles from years to weeks. In addition, SQL Server developers produce the fundamental data layer of code for a project, so other team members must wait for them to finish the database work. As a result, developers must race to complete their work under shorter and shorter deadlines—and for longer and longer hours. But I don't rack up the overtime anymore. I've learned to apply shortcuts to my SQL scripts. How do I get more work done in less time? The templates feature in SQL Server 2000's Query Analyzer utility became a vital resource for shortening my development time.

Why Use Templates?
Everyone uses templates in one form or another. Every time you copy and paste a line of code or a sentence and change the result slightly, you use a template to reduce your workload. Programmers type the same structures in a program over and over, and almost all C and C++ tools include templates that build a skeleton for any application, module, or function. For example, when you add a function to a Visual Basic (VB) form or module, the IDE automatically creates a function template. These days, most development environments generate some type of skeleton routine when you add a function. What about SQL environments?

Native SQL Server tools include features that let you create a form of template by reverse-engineering a script of the code that you need to generate an object. However, when you use these native tools in new development, you must considerably edit the script you generate.

I used to keep a series of files on my desktop, as Figure 1 shows. When I linked these files with WordPad, I could double-click the file, then press Ctrl+A, Ctrl+C, and Alt+F consecutively. Finally, I could press X to close the file, go to SQL Server 7.0's Query Analyzer, and press Ctrl+V to paste all the text from the template file into Query Analyzer. I could then modify this basic template as necessary for my current needs. In this way, I created templates for tables, stored procedures, views, and so on. Although this process worked, it was cumbersome and it made a mess of my desktop.

A couple of years ago, I discovered a third-party tool that substantially shortened my development time. The tool let me move files from my desktop to a folder, then presented a Windows Explorer-like treeview of these files that I could drag onto a script. I immediately abandoned Query Analyzer except in rare instances when I needed a graphical analysis of my query. The third-party tool was only slightly better than keeping the files on my desktop, but it did help me organize files and let me keep a variety of code snippets handy as basic templates.

However, in my current job, I have to make do with a smaller budget and without third-party tools. On my new workstation, I installed SQL Server 2000, opened Query Analyzer, and clicked Object Browser in the menu bar. At the bottom of the Object Browser window, I discovered a tab for Templates. Clicking the Templates tab brings up a view that displays a series of folders containing templates, which Figure 2 shows. Each of these templates is a .tql file that represents a skeleton of code. You can drag these skeletons onto the design window as a basis for development or as an addition to a piece of code. In SQL Server 2000 Query Analyzer, the templates are even more useful than static snippets of code that you insert into your scripts. You can write the templates to accept easily replaceable parameters for a specific script. The parameter feature is a powerful find-and-replace tool—a vast improvement over the bare-bones templates I used to keep on my desktop.

Building Your Own Template
Let's build a template, then use it in a few sections of code to demonstrate its benefits. I use templates in two ways: to develop objects and to reduce the amount of typing that I have to do when I write a section of code.

At the company I work for, I customize the templates that I use for development according to the company's development standard. The templates provide handy development snippets of code that I use when I compile a series of objects on a server. Figure 3 shows my code template for generic stored procedures. This template consists of four sections: drop, create, and permissions sections and a success-or-failure notification section. Every stored procedure that I write includes these four sections because they speed development for everyone in the shop. For the DBAs, the stored-procedure template produces a script that runs and reports a success or failure; for the developers, the template produces consistent documentation. In addition, the "drop" section of the template eliminates errors that result when you try to create an object that already exists.