Data Management

Toying with template parameters in SQL Server 2005

Arthur Fuller details some slick things that you can do with the template parameters in SQL Server 2005. He also points out a potential problem with this feature, which he says shouldn't deter developers from using it.

If you create functions or stored procedures from SQL Server 2005 Management Studio, you will notice that the new window is filled with a template. In general, you get a skeleton interspersed with markers. Listing A is an example created by expanding the Programmability node in the object explorer, selecting Stored Procedures, then right-clicking and selecting New Stored Procedure.

A surprising number of developers fail to read the first block of comments, which give instructions on how to fill in the parameters. You can do so either by pressing [Ctrl][Shift]M or selecting Query | Specify Values For Template Parameters from the main menu. Either way, the dialog box illustrated in Figure 1 is displayed.

To supply a value for any template marker, follow these steps:

  1. Double-click the appropriate line in the dialog box.
  2. Enter a suitable value, and continue on until you have supplied all the values.
  3. Press OK.

The dialog box disappears and the markers are magically substituted for the data you supplied.

The template as written assumes that you will pass two parameters to the stored procedure. It is not readily apparent that before opening this dialog box, you can toy with the template. For example, I added a third parameter to the template code before opening the dialog box. As a result, the dialog box automatically accommodated my new parameter. Figure 2 shows the difference.

This is a very slick feature, although its implementation is a tad frisky. If you press OK when you open the parameter's dialog box, any marker whose properties you do not update will be converted not quite to junk, but it will cease to be a marker.

This minor problem should not dissuade you from using this feature of the editor. Just remember to adjust the templated code before opening the parameter's dialog box. You'll have a nicely formatted standard header for every object you create that is consistent from object to object in your database.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

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!

Editor's Picks

Free Newsletters, In your Inbox