Recently I’ve been working on a project which requires plenty of stored procedures and custom logging. For error handling I don’t want to repeat my logging TSQL statements for each and every SP I write. I created a code snippet and let the IDE code for me. Follow these simple steps to create code snippets in SSMS. The following example inserts pure TSQL code without any parameters and at the end of this blog post I’ve mentioned how to create code snippets which include parameters using one of the built in code snippets of SSMS.

Code snippets are stored as XML files in particular format and imported in to the SSMS. These XML files have the extension of .snippet and you can find the built in code snippets in this location

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033

Let’s call this path as 1033 Snippet Root

In order to get the .snippet file template open one of the simplest built in code snippets from the 1033 Snippet Root. You can find this from the View folder. Under the View folder there’s a code snippet name Create View.snippet

Open it in a text editor, (Notepad++ would be a good choice). This is the structure of a basic TSQL code snippet XML file.

The snippet I created is far more simpler than the above as it does not require any parameters, so I do not need the <Declarations></Declarations> section. Under the <Snippet></Snippet> I have only the <Code></Code> block.

The above code snippet will get the error details of the current execution context and THROW it.

After creating the XML file save it with a .snippet extension. I saved the above as Default Throw.snippet Now we have to import the snippet into the SSMS.

Importing a code snippet into SSMS

Open SSM, go to Tools and then click Code Snippets Manager

Select a folder where you want to put your snippet (This folder structure is simply a categorization of the snippets). If you want you can add a new folder as well. In my case I wanted to put the snippet under My Code Snippets folder. Then click Import. Browse for the snippet file and import it.

That’s all, now we can use the our snippets in the TSQL editor of SSMS. In order to bring the snippet intellisense press Ctrl +K, Ctrl + X and choose the folder then your snippet and press TAB to insert it.