I have found that I have been using the same error handled transaction quite a lot recently. So I wanted created this snippet to have the script easily accessible.

My main goal with this snippet was to have it easily and quickly accessible, with only a few keystrokes. that is why I piggy backed off the SurroundsWith (CTL K + Ctl S)

Here’s how I created it…

Create the Snippet Xml

Snippets work off xml files, here is the snippet Xml I created…

<?xml version="1.0" encoding="utf-8" ?>  
<CodeSnippets  xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">  
<_locDefinition xmlns="urn:locstudio">  
    <_locDefault _loc="locNone" />  
    <_locTag _loc="locData">Title</_locTag>  
    <_locTag _loc="locData">Description</_locTag>  
    <_locTag _loc="locData">Author</_locTag>  
    <_locTag _loc="locData">ToolTip</_locTag>  
   <_locTag _loc="locData">Default</_locTag>  
</_locDefinition>  
<CodeSnippet Format="1.0.0">  
<Header>  
<Title>Transaction Try Catch</Title>  
<Shortcut>trans</Shortcut>  
<Description>A Transaction with a Try-Catch block.</Description>  
<Author>Ellis Miller</Author>  
<SnippetTypes>  
	<SnippetType>SurroundsWith</SnippetType>  
</SnippetTypes>  
</Header>  
<Snippet>  
<Declarations>    
</Declarations>  
<Code Language="SQL"><![CDATA[  
BEGIN TRANSACTION
BEGIN TRY
	-- do stuff here 

$selected$ $end$

	ROLLBACK TRANSACTION
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
	ROLLBACK TRANSACTION
END CATCH  
]]>  
</Code>  
</Snippet>  
</CodeSnippet>  
</CodeSnippets> 

Save with the extension .snippet i.e. TransactionTryCatch.snippet

Xml Breakdown

• Title: This is the name that will show in the snippet dropdown in SSMS.
• Shortcut: This is the text searched by the filter. From what I can tell (and I could be wrong), keyboard shortcuts are not supported in here.
• SnippetType: Here I have used the SurroundsWith so that it is available with the CTL K + Ctl S.
• The snippet script gets added inside the CDATA section.
• $selected$ $end$: These are variables, $selected$ is the currently selected text (if any).

Adding the Snippet to SSMS

I have found that it is best to add the snippet xml file to the existing snippets folder; Function.

The reason to save it to the Function folder is because that is where all the other SurroundsWith snippets are, and if you add it to a different folder (like the obvious My Code Snippets folder) then it creates an extra step to getting the script into the script editor. If every snippet of the same type (SnippetType) is in the same folder, when you do the keyboard shortcut (like CTL K + CTL S) it lists the available functions, but if they are not in the same folder it lists the folders, which need to be selected before the functions are shown.

To find the path to the Function folder, open the Code Snippets Manager (Tools->Code Snippets Manager…) and select the Location.

In theory it is possible to add the file through the Code Snippets Manager, but it didn’t seem to work for me, even with SSMS running with admin permissions.

Note that because the Function folder is in C:\Program Files\ you will need admin permissions to add the file to the folder.

Now the snippet is available.

Accessing the Snippet

In the SSMS Query Editor press the keyboard combination of CTL K + CTL S and in the popup type tra then hit Enter.

Remember that because this is a SurroundsWith snippet, you can also highlight a block of text, and when adding the snippet it will place the highlighted text where the curser is in the image above.