Using and Building Query Analyzer Templates - Page 2

<parameter_name, data_type, value>

where parameter_name is the name of the parameter, data_type is the data type of the parameter, and value is the default value that is to be used to replace every occurrence of the parameter in the shell, provided an alternative is not suggested.

In the Create Database Basic Template there is only one parameter definition, and each occurrence of this parameter looks like "<database_name, sysname, test_db>." This parameter appears in three different places in this template.

Now I could manually change all the parameter options in the QA editor, although that would be slow. To quickly change the parameters there is a Replace Template Parameter menu. This menu can be brought up by clicking on the Replace Template Parameter option under the "Edit" menu, or by entering Ctrl+Shift+M. The Replace Template Parameter menu looks like this:

Note that this menu only displays one parameter. That is because the Database Create Basic Template only contains a single parameter. If there are multiple different parameters in a template, then all parameters would be displayed on this menu.

On the Replace Template Parameter menu you have three different columns. The Parameter column identifies the name of each parameter, the Type column shows what data type is associated with each parameter, and the Value parameter shows what value will be used as a default for each parameter. In this particular example there is one parameter database_name that is of type "sysname," which has a default value of "test_db." The Replace Template Parameter menu retrieves each of these parameters from the code in QA's query pane, whereever it finds a string in the pane that looks like a parameter statement (a "<" followed by a variable, followed by two commas, followed by a closing ">").

The Replace Template Parameter menu is used to replace all the parameters in the QA template. For each parameter this menu allows you to take the default value or enter a value to be used instead of the default. If you want to override the default, type over the default in the value column. Once you are done entering some, or all, of the parameters, you need to click on the "Replace All" button. At this point the template parameter strings will be replaced with the values you entered on the Replace Template Parameter menu.

Since for my example I didn't want to use the default for the "database_name," I typed over "test_db" and entered "DEMO_DB" into the Replace Template Parameter menu like so:

After I click on the "Replace All" button, my QA script looks like this:

-- ============================================= -- Basic Create Database Template -- ============================================= IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'DEMO_DB') DROP DATABASE DEMO_DB GO CREATE DATABASE DEMO_DB GO

Now it is time to add onto this script with another template.

Page 3: Adding Another Template to the Script