TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

SQL Server 2000 Query Analyzer (QA) has a library of
templates that you can use to create stored procedures. The QA interface tends
to hide this feature, which may be why so few developers know it’s there.

When you open QA and then open its object browser, you’ll
see two tabs at the bottom of the browser. Click the Templates tab, and you’ll
see a list of the available templates. Double-click any template, and QA will
open a new query window; then, paste the template into it.

SQL Server ships with a variety of templates designed to
simplify the most common tasks. You’ll find them in the Program Files\Microsoft
SQL Server\80\Tools\Templates\SQL Query Analyzer folder, which contains 13
subfolders that house templates devoted to a particular task (such as Create
Table). Each folder contains several templates; here’s one of the Create Table
templates:

-- =============================================

-- Basic Create Database Template

-- =============================================

IF EXISTS (SELECT * 

....   FROM   master..sysdatabases

....   WHERE  name = N'<database_name, sysname, test_db>')

....DROP DATABASE <database_name, sysname, test_db>

GO



CREATE DATABASE <database_name, sysname, test_db>

GO

The angle brackets in the code denote replaceable markers.
The Replace Template Parameters command on the QA Edit menu invokes a dialog
box that lets you replace all the parameters at one time. Note: The
non-intuitive shortcut to open this dialog box is [Ctrl][Shift]M.

What may not be apparent is that you can combine several
templates into one stored procedure. Once your first template is in its edit
window, just drag any other template into the same window.

Many organizations have standards that govern the style,
indentation, and header of their stored procedures. This is where you can
experience the real power of templates. You can customize existing templates
simply by loading them into your favorite text editor. Even better, you can
create new folders in the SQL Query Analyzer folder and populate them with your
own templates. They’ll show up automatically in the QA object browser. There
are no templates for actions such as DELETE or UPDATE; however, you can roll
your own templates for such actions and add them to the SQL Query Analyzer
folder.

If you work in a multi-developer environment, you can tell
QA to look elsewhere (such as a shared folder on the network) for its
templates. From the QA menu, select Tools | Options and then edit the template
directory setting.

If you have yet to explore these templates, I encourage you
to do so. You’ll find that you save a lot of typing, and, with a little
customization, you can adhere automatically to your organization’s standards.