A Snippet on Snippets

I’ve never used Snippets before, mostly because I have always had a personal script repository stored in the cloud along with Intellisense or a SQL Formatting add-on. Lately though, I’ve found it a bit easier to store commonly run scripts within Management Studio using Solution Explorer. I mentioned this in a post that Aaron Bertrand reviewed for me, and he suggested Snippets as an alternative. Snippets are something I’ve been meaning to look into for a while. These are only available in SQL Server 2012 and later, but I’ve had access to that at work for about two years, so I’m long overdue to explore this feature.

If you right-click in Management Studio, you have the option to Insert Snippet, which then provides you with a unique option list of sub folders with snippet files.

InsertSnippet

InsertSnippet2

Choose a snippet, and you will get a default script similar to this:

CreateSampleProcedure

Now, the really neat thing about snippets lies in the highlighted sections in the image above. If you update the parameter names in the declaration, they will automatically update in the select statement as well. That could save some time and naming issues while creating a new procedure. The best way to learn exactly how to recreate this feature is by reading the existing snippet files for examples.

I assumed adding a new snippet would be a breeze. I assumed I’d be able to set everything up with a bit of click-guessing, and I’d be snippet shortcutting my way to scripting simplicity in no time. I assumed wrong. Don’t jump into snippets blindly.

I did.

If you click on Tools, you can select Code Snippets Manager. You will then see all the existing folders with their corresponding snippets. I want to create new snippets though, so I’ll try Add, navigate to the folder where I store my scripts, and Select Folder.

CodeSnippetManager

Add

This is the Snippet Manager after I have successfully Added my…empty…folder

Alright, it imported the Folder into the viewer, but it’s empty. I know there are SQL scripts in that folder – what’s wrong? Let’s try Import. Navigate to the folder again…and it’s still empty! What’s the big idea!?

ImportSql

After calming down, I looked carefully at the above screen.  If you notice in the bottom right corner, the only available extension is .snippet. I tried to select other types, but that drop down menu is a lie. There are no options. I know that drop down menu is default behavior for Windows, but all it did was lull me into a false sense of freedom for being able to choose other file types.

At this point, it’s obvious that I should not be trying to add snippets on a whim. I highly suggest you do a bit of reading on snippets or at least find the existing snippets and open a file or two to see how they are formatted. You can find the exact location by using the Code Snippets Manager. Click on one of the default folders, and it will display the file path in the Location header. The screenshot of the Manager above has the file path displayed. It will save a lot of time and confusion if you choose to read a bit before just trying to make it work. Learn from my mistakes.

Still, I tried to make it work. I made a new snippet file with only a SQL script inside. By doing this, I was finally able to add a file, but immediately received the following error.

InvalidFormat

Only then did I give up and read the documentation. I like to poke around at a new feature for a bit before reading all about it, as it helps me understand everything while later researching because I’ve at least seen the options before. This was one of those times where it was a much worse idea than normal to work that way.

Turns out, once I created the .snippet file, I still needed to add XML notation to it so that SSMS could read the file. Any folder I Add will auto populate with new snippets saved to it. That’s pretty handy. I’m no XML wiz, but the below code can be copied and pasted into each snippet, with only minimal, obvious changes.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
	<CodeSnippet Format="1.0.0">
		<Header>
			<Title>Snippet Title</Title>
            <Shortcut></Shortcut>
			<Description>Code Snippet for X construct.</Description>
			<Author>Author's Name</Author>
			<SnippetTypes>
            <SnippetType>Expansion
			</SnippetTypes>
		</Header>
		<Snippet>
			<Declarations>
                <Literal>
                <ID>Condition</ID>
                <ToolTip>Condition to evaluate</ToolTip>
                <Default>Condition</Default>
                </Literal>
			</Declarations>
			<Code Language="SQL">
			<![CDATA[
--SQL Script Here
$condition$ --This will have a Tool Tip as defined above.
$selected$
--Code following will appear after selected text.

]]>
			</Code>
		</Snippet>
	</CodeSnippet>
</CodeSnippets>

You should edit the Description tag for each snippet and then just copy and paste the SQL inside the code header. The Author tag will be useful to identify snippets, but once you have a default script saved, that should not require upkeep. The ability to make shortcuts is awesome, but I doubt I’ll remember the keyboard combination and use it enough to warrant a shortcut.

The SnippetType should be Expansion (for a regular snippet) or SurroundsWith. You should look at the existing SurroundsWith snippets before crafting your own if you opt for that choice.  There are variables, like $selected$

, that you should include in a SurroundsWith snippet.  That variable will allow you to select a script, then add the SurroundsWith snippet to place the inserted code around the selected text. Omitting the variable will cause the SurroundsWith snippet to just replace the selected script – not very helpful. To select a SurroundsWith snippet, you will need to right-click in the Query Pane and choose Surround With, which is directly underneath the Insert Snippet command. You won’t have to drill through as many sub menus if you choose SurroundsWith, because there are fewer snippets there.

The Declarations tag allows you to identify objects in the string that will have a tooltip. The ID tag should match a variable name used in the Code tag, with the ToolTip tag being the text you want displayed when hovering over the text. For example, the Create Table snippet has an ID tag of $TableName$, and after inserting the snippet, the ToolTip identifies its purpose.

SnippetHover

Finally armed with a valid snippet, I went back to Code Snippets Manager. Now I can see my sample script along with the tagged values.

SnippetTitle

Adding a snippet for BEGIN TRAN would be a great way to test out the different SnippetTypes. Create the snippet as an Expansion first with no special variables. Next edit the file and change the SnippetType to SurroundsWith and add the $selected$ variable between your BEGIN TRAN and ROLLBACK/COMMIT lines. Highlight a SQL  statement and insert the snippet through the SurroundsWith option. Notice that this time, the snippet indeed surrounded the statement. You can include a variable in an Expansion snippet, but it’s best to categorize correctly, so that you can find a snippet next time with minimal effort.

Summary Snippets definitely have some merits, but it takes a bit of work and understanding to create them. Make sure to source control or at least back them up once created. For anyone with a SQL formatting add-on, snippets probably aren’t overly exciting (but neither was Intellisense). Anyone using the native environment though, it’s your only way to create code shortcuts.

The interface is infinitely more complicated than it should be, but so are many other aspects of Management Studio. You should be able to click Add in the Code Snippets Manager, paste in a script, select Expansion or SurroundsWith as the Snippet Type, and then save it to the menu level of your choice. Alternatively, you should be able to Import saved SQL files to be snippets without any special formatting. Then add in the special variables as desired.

I originally thought snippets could be a replacement for how I stored most of my scripts, but instead, it’s more appropriate as shortcuts for only very popular code. Except for a few constantly used scripts, I doubt the easy access will outweigh the setup costs to use snippets for me. I know it’s not overly difficult to copy and paste the XML into a new file, but snippets are all about being lazy in the first place!

Advertisements