To create this template, first open Query Analyzer and enter the desired text in the design window. Second, after you enter the text, click File, Save from the menu. When the File dialog box appears, enter the template's name. Third, change SQL Query (*.sql) files in the Save as Type drop-down list to Template SQL (*.tql) files. Next, if you installed SQL Server on the C drive, open the C:\program files\microsoft sql server\80\tools\templates\sql query analyzer folder. (If SQL Server isn't installed on the C drive, replace C with the name of the drive on which you installed SQL Server.) This folder already contains folders that correspond to the default templates that are installed with the tool. Then, after you save your template and right-click to refresh the treeview in the left pane, your template appears. (Figure 4 shows a template that I created by following the path I just described. In Figure 4, all the files below view_template are templates that I created.) Finally, press Ctrl+N to open a new query window, then drag your new template onto the window. The outline of a stored procedure appears.
You can perform even more tricks with this amazing Query Analyzer template tool. Figure 4 shows the template (minus proprietary information) that I use frequently. If you look closely, you can see code that follows the format parameter name, type, default value. This section of code, which takes advantage of the parameter search-and-replace feature in Query Analyzer, is a big time-saver. The code lets you include a series of parameters in your template, then replace them quickly and easily. Table 1, page 54, shows the example's three parameters.
You can change the parameter names for each project. Also, the type names you include are for programmer reference and don't affect the values you replace in the template. After you drop this template into the query window and press Shift+Ctrl+M, the Replace Template Parameters dialog box, which Figure 5 shows, appears. The dialog box lets you change the default values in the third column to suit the batch you're working on. Then, you can type the new name for the procedure, press the down arrow twice (I usually leave my name), and type the current date into the dialog box. Finally, click Replace All.
Before I began building my templates, I examined many of my existing procedures and looked for repeated patterns or sections that I changed in every procedure. Then, I set the repeating items as parameters in my templates. By using this technique, I shaved minutes off the development time for every stored procedure.
Speedy Joins
Templates are useful for more than creating new procedures. Not only did I save time in developing new stored procedures but I also quickly came upon another benefit of templates in my daily development: building faster queries. In my job, one of the databases is in third normal form (3NF), and four central tables recur in most queries that I run. Figure 6 shows the tables (with their main keys). I often join the tables as follows:
FROM product p, subcategory s, productattributes pa, producttype pt
WHERE p.productid = s.productid
AND s.categoryid = pt.categoryid
AND pt.productid = p.productid
AND p.productid = pa.productid
Typing these joins repeatedly was extremely annoying. Fortunately, I had Query Analyzer 2000 by this time, or I might have had to start renaming tables and altering the schema to keep my sanity. I created a template with the join example above and saved it in the root of the templates folder on my workstation. Because I keep the same aliases in all my queries for readability, I could drag this code snippet into any script I needed to join these tables together. This template became so useful that I even started to use it when I had to troubleshoot problems. When someone called with a data problem, I could quickly drag the template, add another table or two, add another qualification to the WHERE clause, and call the problem solved.
Free Up Your Personal Time
I love working with SQL Server and developing solutions. Spending time writing code, finding a solution to a problem, doing some testing and tweaking, and delivering a piece of code that works are aspects of my job that I relish. But although I enjoy my work, I enjoy my family more. My hat is off to the SQL Server development team for the improvements they made to Query Analyzer—especially for the templates feature. If you don't use templates in your work, I encourage you to do so. And if you're a SQL Server developer who isn't using SQL Server 2000 yet, I recommend that you examine SQL Server 2000 and its enhanced Query Analyzer. The time savings I realize when I use templates are worth the price of the software, and including templates in my work greatly increases my productivity.