Tuesday, 13 September 2011

Effective TSQL Templates

What are TSQL Templates?

If you haven't discovered TSQL templates yet, you've been missing out on an extremely useful feature of SQL Server Management Studio (SSMS). We'll get into why they're so good very shortly but lets start by opening up the Template Explorer so you can see what they are; click View → Template Explorer:

Or if you're like me and you like your keyboard shortcuts: Ctrl + Alt + T

Though this shortcut is strangely missing in SSMS in Denali CTP3. This will open up the Template Explorer that has dozens of TSQL templates to build from. Go ahead and explore them.

Template Replacements

Using the "Alter Stored Procedure Template" as an example (its under the Stored Procedure folder of the Template Explorer), you will see many of the templates have some strange looking code amongst them. It looks tenuously like XML in that the bits of code appear to be tags surrounded by < and >

For example:

What are these exactly?

Well, they're a handy way to allow you specify replacement values in your template. More commonly known as template parameters. With the "Alter Stored Procedure Template" you are always going to need to specify the procedure you want to modify. The fully qualified name takes the form schema.object_name, in the example above I have highlighted the piece of code that allows for a common replacement for a schema name. You might be thinking "that looks awfully complicated for something that could be specified simply with <schema>". There's a reason for this; we have the ability to specify both a data-type and a default value for the replacement.

The full "syntax" for the template parameters is:
<Parameter_Name, Data_Type, Default_Value>

Whilst you have the ability to specify a data type, this actually plays no meaning (as far as I can tell). It simply helps you identify the kind of data you are expecting to replace here. You can even leave the data type and default value sections empty if you like.

Setting Template Parameter Values

How do we go about actually using these template parameters? Click Query → Specify Values For Template Parameters:

Or use the keyboard shortcut Ctrl + Shift + M
Yet another missing shortcut from Denali.

When you initiate the "Specify Values for Template Parameters" you'll receive the following dialog box:

If you enter some values and click ok, you'll find the values replaced in your template:

Modifying the default templates

After you've experimented with the default templates, you'll soon discover that you want to:
  • Modify existing templates - either to add your own spin to them or adhere to your coding standards
  • Add templates of your own
If you right click on one of the templates, rather than double clicking, you'll see an "edit" option:

Selecting edit will open the template, as per earlier, and allow you to make modifications to it. Lets add a comment block and save the changes:

Save your changes.

Now reopen your template.

This is all great.  Right up until you edit any of the "... (New Menu)" templates.  For example; if you edit the "Create Procedure (New Menu)" template, save and reopen you'll find everything looks as if it should.  And perhaps you did this following my instructions above.  But now open a database, navigate to the Stored Procedures in that database, right click and select "New Stored Procedure":

You'll find the original "...(New Menu)" template is opened.  None of your changes have propagated through.  Why is this?

Well the answer is quite simple, and arguably a bug but we'll settle for it being labelled as a feature.

When you edit any of the templates you edit the templates stored in: %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

This seems to be the path used when you open a template through the template explorer too. Unfortunately, when you use the "New Menu" options through SSMS it doesn't use your profile copy, but the original install copy found in: C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

To me; this a little bit crazy and a hugely counter intuitive. But at least we can "fix" this by updating these versions. T-SQL templates are a useful feature, especially if you want to standardise a few areas such as comment blocks. But as you'll have seen from the array of pre-supplied templates you can use them to build up an array of shortcuts for building your code. These are further extended in Denali, or by 3rd party tools including SSMS Tools (which is free), by the use of code snippets.

An effective "New Menu" template

Now that you know where to update the templates to affect the "New ..." option from SSMS its up to you to go and create your effective templates. But to help you, here's one I use for my coding that I find useful:

--use <DBName, sysname, >;

if not exists(select 1 from sys.objects where object_id = object_id('[<Schema, sysname, dbo>].[<Object Name, sysname,>]')
	exec sp_executeSQL N'create procedure [<Schema, sysname, dbo>].[<Object Name, sysname,>] as select ''Procedure Stub'';';

alter procedure [<Schema, sysname, dbo>].[<Object Name, sysname,>]
 * Procedure:	[<Schema, sysname, dbo>].[<Object Name, sysname,>]
 * Date:		<Created Date, datetime, >
 * Author:		<Created By, ,Your Name>
 * Description:	
 * ------------ --------------- ----------------------------------------------------
 * Date			Author			Description
 * ------------ --------------- ----------------------------------------------------
 * <Created Date, datetime, >	<Created By, ,Your Name>	
 * ------------ --------------- ----------------------------------------------------
 * Procedure Options
set nocount on;

 * Start transaction
begin transaction <Schema, sysname, dbo>_<Object Name, sysname,>;

 * Do the work

 * Commit transaction
commit transaction <Schema, sysname, dbo>_<Object Name, sysname,>;

 * End of procedure

I recommend you amend the "New Menu" templates at the very least as these are the easiest ones to use. And they're likely to be the ones you reference the most if not the only ones you reference. What you define as your template is up to you, but do think about what you want to always include. Its a good opportunity to encourage comments or at the very least comment blocks in stored procedures, functions and views. I'll be writing a blog post about effective commenting in the coming weeks and setting up some templates will help in this area.

1 comment:

  1. Data Recovery services for hard disk drives, SSD, RAID, NAS, USB flash drives, memory cards and mobile phones in New Zealand. Lab based in Auckland. data recovery