Creating SQL Server 2012 Code Snippets

FINALLY!!  After much anticipation Microsoft have finally introduced code snippets into the latest version of SQL Server.

How much time does one spend writing out the same code over and over to create objects or how much time is spent fixing errors made by copy and paste!

Code snippets eliminates these problems and creating them is simple.

Where are they located?

Code Snippets are in 2 locations. 

 If you create your own the default save location will be in the Customer Snippets directory but it is very possible to edit and/or add to the Default Snippets.

Snippets have a file extension of .SNIPPET but are simple XML files.

General XML layout.

locDefinitions / locTag - Title, Description, Author, Tool-tip.  These will be displayed in the code snippets manager and in the snippets selection list

    Next are the Literals - these are pretty much variables which can be used in the CDATA part of the file.  The format of a Literal is:

These literals are then used to build the SQL statement:

Note that the Literals start and end with the $ symbol.

This will produce the following output:

Notice the pop-up when hovering the cursor over the data type.

It seems that one of the main purposes with using Literals are the ability to tab through them.  As you can see from the above image, each Literal is highlighted yellow showing that they are tabbable. Unfortunately if you use the same Literal multiple times throughout your script you can only tab the first one.  Tabbing order is defined by first literal in the script to the last.  Pressing the "Enter" key will accept the changes to the Literals and the ability to tab will end.

Creating your own Snippets

The above is the basic layout of the .SNIPPET file, creating your own is easy, either create a new text file in a new folder (see below for creating a new folder) or an existing folder, fill out the Title, description etc, define any literals and fill out the CDATA.  Rremember to save the file as .SNIPPET. If you are not creating the new file under an existing directory under the 1033 folder then you will need to open the Snippets Manager and "Import" your new snippet file.  When importing your snippet file you will get the option to select an existing folder to save it in.

It's worth noting that if you are creating your own files under an existing folder then once the file is added to the folder (and it's valid) then the file will immediately be available to use.

Taking code snippets one step further

Code snippets are great as they are but its possible to make them even better by using parameters.  Parameters are not new, they are used in SQL templates.  Pressing Shift+CTRL+M will bring up the "Specify Values for Template Parameters" box where you can quickly fill out the templates parameters which can save huge amounts of time!

The general syntax for a parameter is:

<Parameter Name, Type, Value>

e.g.

<Table Name, sysname, Enter the name of the table>

This would appear as so:

Its possible to use this parameter format in the CDATA when building your SQL statement.  Although it's not exactly eye friendly, it makes filling out your snippets easier then ever !

Here's one example of one of my Stored Procedure:

By using these parameters I can easily fill out the most boring parts of my stored procedure in under a minute leaving just the nice meaty stuff left to do.

Adding folders to the Snippet Menu:

To add a new folder to the snippet menu simply reference it in the SnippetsIndex.xml file.

Add a new SnippetDir to the language element.

<SnippetDir>   <OnOff>On</OnOff>   <Installed>true</Installed>   <Locale>LOCALE_ID</Locale>   <DirPath>%InstallRoot%\VSTSDB\Snippets\%LCID%\Name of the folder\</DirPath>   <LocalizedName>Name of the folder</LocalizedName> <SnippetDir>

Once added, create a folder of the same name in the root of the 1033 folder.

Next open the Code Snippets Manager and add the newly created folder.  This will also import any files under the folder.

And there you have it!  Creating your own SQL 11 Code Snippets.