Post
Ask Question

Using Code Snippets In SQL Server

A new feature in SQL Server 2012 is code snippets. If we are coding in any programming language, the chances are that we’re constantly repeating similar code. Repeating the same code required more efforts and sometimes it become distasteful.

This issue has been removed for SQL Server developers after introducing the code snippets features into SQL Server 2012. In this article I will explain how to use predefined code snippets and modify the code snippets.

How to use Code Snippets

To insert a code snippet into query window go through the Menu Edit, IntelliSense, then Insert Snippet…, as shown below.

menu

Another method is to “Right Click” on the query window and snippets option.

Right Click

SQL Server contain two type of snippets.

  • Expansion snippets will simply insert text in the position of the cursor or replace selected text.
  • Surround With snippets will insert text before and after the cursor or selected text.

It totally depends upon you, you can select any option. If you select the “Insert Snippets” option then below option list will open.

Snippets

From the above list you can select any type of snippets that you require. There are multiple code snippets for each object, so you are just required to navigate through the folders and select the desired snippet.

Snippets

After selecting the desired code snippets you will find a code snippet into your SQL window.

sql

This code snippet contain multiple fields and you have the ability to edit these fields as per your requirement.

sql

Not only can we insert script templates, but we can also surround your code with either BEGIN, WHILE or IF blocks.

blocks

For surrounded line select “Surround With” option and choose desired code snippets template.

code

Customize the Code Snippets

It may be possible that the default snippets might not be exactly what we need, which is exactly why we can create our own. We can manage all code snippets using the Code Snippets Manager. To launch the Code Snippets Manager go to Tools and click on “Code Snippets Manager..” option.

Code Snippets Manager

In Code Snippets Manager, we can view all available templates (code snippets), we can also add, remove and import the code snippets. Snippets are simply xml files with a special extension. To find out the structure of snippets template, search for location path of that particular snippet.

Code Snippets Manager

Template(XML file) for table code snippets is stored here “C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SQL\Snippets\1033\Table\Create Table.snippet”.

Now, I will explain the basic structure of code snippets. I will use code snippets of “Create Table” templates.

  1. <?xml version="1.0" encoding="utf-8" ?>  
  2.     <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">  
  3.         <_locDefinition xmlns="urn:locstudio">  
  4.             <_locDefault _loc="locNone" />  
  5.             <_locTag _loc="locData">Title</_locTag>  
  6.             <_locTag _loc="locData">Description</_locTag>  
  7.             <_locTag _loc="locData">Author</_locTag>  
  8.             <_locTag _loc="locData">ToolTip</_locTag>  
  9.         </_locDefinition>  
  10.         <CodeSnippet Format="1.0.0">  
  11.             <Header>  
  12.                 <Title>CreateTable</Title>  
  13.                 <Shortcut></Shortcut>  
  14.                 <Description>Creates a table.</Description>  
  15.                 <Author>Microsoft Corporation</Author>  
  16.                 <SnippetTypes>  
  17.                     <SnippetType>Expansion</SnippetType>  
  18.                 </SnippetTypes>  
  19.             </Header>  
  20.             <Snippet>  
  21.                 <Declarations>  
  22.                     <Literal>  
  23.                         <ID>SchemaName</ID>  
  24.                         <ToolTip>Name of the schema</ToolTip>  
  25.                         <Default>dbo</Default>  
  26.                     </Literal>  
  27.                     <Literal>  
  28.                         <ID>Tablename</ID>  
  29.                         <ToolTip>Name of the table</ToolTip>  
  30.                         <Default>Sample_Table</Default>  
  31.                     </Literal>  
  32.                     <Literal>  
  33.                         <ID>column1</ID>  
  34.                         <ToolTip>Name of the column</ToolTip>  
  35.                         <Default>column_1</Default>  
  36.                     </Literal>  
  37.                     <Literal>  
  38.                         <ID>datatype1</ID>  
  39.                         <ToolTip>Data typeof the column</ToolTip>  
  40.                         <Default>intNOTNULL</Default>  
  41.                     </Literal>  
  42.                     <Literal>  
  43.                         <ID>column2</ID>  
  44.                         <ToolTip>Name of the column</ToolTip>  
  45.                         <Default>column_2</Default>  
  46.                     </Literal>  
  47.                     <Literal>  
  48.                         <ID>datatype2</ID>  
  49.                         <ToolTip>Data typeof the column</ToolTip>  
  50.                         <Default>intNULL</Default>  
  51.                     </Literal>  
  52.                 </Declarations>  
  53.                 <Code Language="SQL">  
  54. <![CDATA[CREATE TABLE $SchemaName$.$Tablename$  
  55. (  
  56. $column1$ $datatype1$,  
  57. $column2$ $datatype2$  
  58. );$end$]]>  
  59. </Code> </Snippet>  
  60.         </CodeSnippet>  
  61.     </CodeSnippets>  
In starting we find the XML declaration tag that specifies the version and encoding. This is strongly recommended for each XML file. After that we find the Code Snippets that contains all the snippet definition and contains the namespace for snippets. A code snippet file can contain a single “Code Snippets” tag. The Code Snippets tag contains mainly two main tags. First one is the Header tag that define the title, shortcut, description, Author of code snippets and snippets type. You can also modify these tags.

The second tag is “Snippets”, this most important tag of code snippets, the Snippet section has the actual code of the snippet. The Snippets tag has two parts: Declaration and Code. Declaration part allow us to declare the variables that will be used in the code section. Variable declarations are called literals. Literal tag contain three properties.
  • ID: Unique id for literal and should not be repeated.
  • Tooltip: Provide a brief hint information about literal , what should the literal be when the literal is selected.
  • Default: define text that will show up in the snippet when inserted.
Code Section mainly containss the definition of code snippets and use the literals that are defined above in declaration section. Code section defines the Language (SQL or XML), code is inserted between “<![CDATA[“ and “]]>”. In code section $end$, $selected$ are reserved words. The $end$ word defines the  location to place the cursor after the code snippet is inserted and $selected$ word represents text selected in the document that is to be inserted into the snippet when it is invoked, mainly use in Surround With snippets.

Customize the Code Snippets:

I think the above explain is enough to understand the code of a code snippets template,  now I am going to modify a code snippet. I am going to modify the code snippets of “Create Table” template. By default when we implement the “Create Table” template then following code is retrieved.

Table

Now, I want these code snippets to contain three more columns with data type of int, varchar and date data types. Go to the location of this code snippets and open the code snippets into any IDE tools. Add 3 literals for column names and 3 literals for data types as below.

literals

Now we need to change the code snippet and use the literals which we created into declaration section as below,

code

Complete code snippets are:

  1. <?xmlversion="1.0"encoding="utf-8" ?>  
  2.     <CodeSnippetsxmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">  
  3.         <_locDefinitionxmlns="urn:locstudio">  
  4.             <_locDefault_loc="locNone" />  
  5.             <_locTag_loc="locData">Title</_locTag>  
  6.                 <_locTag_loc="locData">Description</_locTag>  
  7.                     <_locTag_loc="locData">Author</_locTag>  
  8.                         <_locTag_loc="locData">ToolTip</_locTag>  
  9.                             </_locDefinition>  
  10.                             <CodeSnippetFormat="1.0.0">  
  11.                                 <Header>  
  12.                                     <Title>Create Table</Title>  
  13.                                     <Shortcut></Shortcut>  
  14.                                     <Description>Creates a table. Contains 5 columns. </Description>  
  15.                                     <Author>Pankaj Choudhary</Author>  
  16.                                     <SnippetTypes>  
  17.                                         <SnippetType>Expansion</SnippetType>  
  18.                                     </SnippetTypes>  
  19.                                 </Header>  
  20.                                 <Snippet>  
  21.                                     <Declarations>  
  22.                                         <Literal>  
  23.                                             <ID>SchemaName</ID>  
  24.                                             <ToolTip>Name of the schema</ToolTip>  
  25.                                             <Default>dbo</Default>  
  26.                                         </Literal>  
  27.                                         <Literal>  
  28.                                             <ID>Tablename</ID>  
  29.                                             <ToolTip>Name of the table</ToolTip>  
  30.                                             <Default>Sample_Table</Default>  
  31.                                         </Literal>  
  32.                                         <Literal>  
  33.                                             <ID>column1</ID>  
  34.                                             <ToolTip>Name of the column</ToolTip>  
  35.                                             <Default>column_1</Default>  
  36.                                         </Literal>  
  37.                                         <Literal>  
  38.                                             <ID>datatype1</ID>  
  39.                                             <ToolTip>Data type of the column</ToolTip>  
  40.                                             <Default>int NOT NULL</Default>  
  41.                                         </Literal>  
  42.                                         <Literal>  
  43.                                             <ID>column2</ID>  
  44.                                             <ToolTip>Name of the column</ToolTip>  
  45.                                             <Default>column_2</Default>  
  46.                                         </Literal>  
  47.                                         <Literal>  
  48.                                             <ID>datatype2</ID>  
  49.                                             <ToolTip>Data type of the column</ToolTip>  
  50.                                             <Default>int NULL</Default>  
  51.                                         </Literal>  
  52.                                         <Literal>  
  53.                                             <ID>column3</ID>  
  54.                                             <ToolTip>Name of the column</ToolTip>  
  55.                                             <Default>column_3</Default>  
  56.                                         </Literal>  
  57.                                         <Literal>  
  58.                                             <ID>column4</ID>  
  59.                                             <ToolTip>Name of the column</ToolTip>  
  60.                                             <Default>column_4</Default>  
  61.                                         </Literal>  
  62.                                         <Literal>  
  63.                                             <ID>column5</ID>  
  64.                                             <ToolTip>Name of the column</ToolTip>  
  65.                                             <Default>column_5</Default>  
  66.                                         </Literal>  
  67.                                         <Literal>  
  68.                                             <ID>datatype3</ID>  
  69.                                             <ToolTip>Data type of the column</ToolTip>  
  70.                                             <Default>int NULL</Default>  
  71.                                         </Literal>  
  72.                                         <Literal>  
  73.                                             <ID>datatype4</ID>  
  74.                                             <ToolTip>Data type of the column</ToolTip>  
  75.                                             <Default>varchar(max) NULL</Default>  
  76.                                         </Literal>  
  77.                                         <Literal>  
  78.                                             <ID>datatype5</ID>  
  79.                                             <ToolTip>Data type of the column</ToolTip>  
  80.                                             <Default>[date] NULL</Default>  
  81.                                         </Literal>  
  82.                                     </Declarations>  
  83.                                     <CodeLanguage="SQL">  
  84.                                         <![CDATA[CREATE TABLE $SchemaName$.$Tablename$  
  85. (  
  86. $column1$ $datatype1$,  
  87. $column2$ $datatype2$,  
  88. $column3$ $datatype3$,  
  89. $column4$ $datatype4$,  
  90. $column5$ $datatype5$  
  91. );$end$]]>  
  92.                                         </Code>  
  93.                                 </Snippet>  
  94.                                 </CodeSnippet>  
  95.                                 </CodeSnippets>  
code

Once all the modification is done, now we need to save the changes and import the code snippets in the query window.

query

When we select the “Create Table” Template , notice that 3 new columns are added into “Create Table” snippets.

Create Table

Thanks for reading the article.

Read more articles on SQL Server:


Similar Articles