Build stored procedures with templates

from the Artful SQL Server & Access Tips List

SQL Server Query Analyzer (QA) ships with a library of templates you can use to create stored procedures. Oddly, the QA interface tends to hide this feature. That may be why more developers don't know it's there.

Open QA, then open its object browser, and you see two tabs at the bottom. Clicking the Templates tab brings up a list of available templates. If you double-click one, QA opens a new query window with the template into it.

For SQL Server 2000 the templates are in %ProgramFiles%\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer; for SQL Server 2005 they are in %ProgramFiles%\Microsoft SQL Server\90\Tools\Templates\SQL Query Analyzer. Various subfolders group them. Here is a 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

Angle brackets denote replaceable markers. The Replace Template Parameters command on the QA Edit menu [Ctrl Shift M] invokes a dialog that permits replacement of all parameters at once.

You can combine several templates into one stored procedure. Once a template is in its edit window, just drag any other template into the same window.

Many organizations have standards for stored procedure style, indentation, and headers. Here you 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.
Return to the Artful SQL Server & Access tips page