Code snippets allow you to quickly insert code templates into your query window to perform common TSQL tasks. By using a keyword short cut you can invoke the code snippet window and select the desired code snippet. This will copy the pre-defined code template to your query with placeholders which clearly indicates the locations where you need to make changes.

Inserting a code snippet

You can activate the code snippet window by using the keyboard shortcut CTRL+K followed by CTRL+X. Alternatively, you can select the Insert Snippet.. menu item from the Edit >> IntelliSense menu.

Code snippets are organized by categories. Select a category to see the available code snippets under that category. For this example, let us select “Function”.

Let us select “Create Inline Table Function” and SSMS will insert the code required to create a Table Valued Function to your query window at the current cursor position.

After inserting the function template, SSMS will also highlight the placeholders that need to be modified. This will help you to identify the places you need to make changes.

Creating your own code snippet

SSMS comes with some pre-defined code snippets. It is quite easy to add your own code snippets to SSMS and speed up developing commonly used TSQL code. To do this, you need to create a .snippet file containing your code and register it with the Code Snippet Manager. A .snippet file is an XML file that follows a pre-defined schema.

Let us create a TSQL code snippet to generate the basic code for a simple TRY-CATCH block. Here is the code that we will include in the new code snippet.

BEGIN TRY
	-- Your code here
END TRY
BEGIN CATCH
	-- Your error handling here
	;THROW
END CATCH

Here is the code snippet file created for inserting the above code fragment.

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>try-catch</Title>
      <Author>jacob</Author>
      <Description>
      </Description>
      <HelpUrl>
      </HelpUrl>
      <Shortcut>
      </Shortcut>
    </Header>
    <Snippet>
      <Code Language="sql"><![CDATA[
		BEGIN TRY
			-- Your code here
		END TRY
		BEGIN CATCH
			-- Your error handling here
			;THROW
		END CATCH
		]]>
	  </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>

There are several tools available that will allow you to create code snippets easily. One such tool is Snippet Designer available at codeplex. This allows you to create code snippets right within visual studio.

Registering a code snippet

After creating a code snippet file, you need to register it with SSMS. This can be done using the “Code Snippet Manager”. Launch the “Code Snippet Manager” from the “Tools” menu.

This will open the code snippet manager window.

The Code Snippet Manager shows all the code snippets registered with SSMS. You can use the Add and Remove buttons to register new code snippets or remove the code snippets already registered.

Let us register the code snippet we created in the previous step. Click on the Add button and select the folder where you have put your code snippet files. After selecting the code snippet folder, all the code snippets available within the selected folder will show up on the Code Snippet Manager.

Testing the newly added code snippet

It is time for us to test the code snippet we just created and registered. Switch back to SSMS query window and press CTRL+K, CTRL+X and it will open the code snippet prompt as given below.

Select the code snippet “try-catch” and it will insert the TSQL code we placed within the snippet file at the current cursor position within the SSMS query window.

The code given above is very basic and used for the demonstration purpose only. You might end up creating more complex code snippets in most real world scenarios. The Snippet Designer tool mentioned earlier is quite capable of creating code snippets for Visual Studio as well as SSMS.