Saturday, January 7, 2017

SQL Server - Adding Code Snippet and Using Existing Code Snippet

Once I wrote a post on SQL Server Template Explorer that describes available TSQL templates and how they can be used. Just like templates, we have been given some ready-made Code Snippets that help us to construct the statements easily. Not only that, it allows us to add our own snippets using adding Code Snippet Manager.

First of all, let's see how we can use existing code snippets. Assume that you need to create a SQL Login and you cannot remember the syntax. What you can do is;

    1. Either select Insert snippet... context menu in the Query Window or press Ctrl+K and Ctrl+X  (Press Ctrl and hold, and then press K and X).

    

    2. Select Login folder and then select Create SQL Authentication Login.
    
    3. Change the code as you need.

    
Note that, like adding codes using Template Explorer, you do not get another interface for changing values. Values need to be manually changed.

If you need to add your own code snippet, you can take copy of an existing one, change as you want and save with your own name. Assume that you need to add Azure Firewall Setting as a code snippet. If so, here are the steps;

    1. Open the SQL Server Code Snippet folder. If you have selected the default location when installing SQL Server, the path would be C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets\1033.

    2. Create a folder called Azure (or name as you need).


    3. Update the SnippetsIndex.xml. Add the following node to the file. This node is for the newly created folder. (** Note that this file cannot be modified if you have not opened the editor as Administrator. If change this using Notepad, open the Notepad as an Administrator and then open the file for modifictions).


    4. Take a copy of existing snippet and place in Azure folder. I have taken Create SQL Authentication Login.snippet and renamed as Create Azure Server Level Firewall Rule.

    5. Open Create Azure Server Level Firewall Rule file (Open as an Administrator) and modify , <description> and <author> under <header> with your details.</p> </div> <div data-blogger-escaped-style="text-align: left;" style="text-align: left;"> <p> <br></p> <p>     </p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://2.bp.blogspot.com/--87E4fPLYno/WHDpkERA-6I/AAAAAAAAEk0/Qva74b-oX8gc3g6rKTZ2mDISkj4Vnv8egCLcB/s1600/Code%2BSnippet%2B06.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://2.bp.blogspot.com/--87E4fPLYno/WHDpkERA-6I/AAAAAAAAEk0/Qva74b-oX8gc3g6rKTZ2mDISkj4Vnv8egCLcB/s640/Code%2BSnippet%2B06.png" border="0" width="640" height="360"></a></p> <p> <br></p> <p>     6. Modify <declaration> node and <code> node as per the snippet you need to add. In this example, the required code is EXEC sp_set_firewall_rule and it needs three parameters: <i>Rule name, Starting parameter </i>and<i> Ending parameter</i>. Parameters have to be added as <literal> and the code has to be added in the <code> node. Here is the way of adding this SP.</p> <p> <br></p> <p>     </p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://3.bp.blogspot.com/-Iol1Gfy3KfE/WHDqlB8EqmI/AAAAAAAAEk8/9-gbd2RyAH0nBzHjGCUtBEdqjBORaFnHgCLcB/s1600/Code%2BSnippet%2B07.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://3.bp.blogspot.com/-Iol1Gfy3KfE/WHDqlB8EqmI/AAAAAAAAEk8/9-gbd2RyAH0nBzHjGCUtBEdqjBORaFnHgCLcB/s640/Code%2BSnippet%2B07.png" border="0" width="640" height="410"></a></p> <p> <br></p> <p>     7. Done. Now the folder that contains the snippet has to be added to the <i>Code Snippet Manager</i>. Open <i>Management Studio</i> and select <i>Code Snippet Manager</i> menu item in the <i>Tools</i> menu.</p> <p> <br></p> <p>     8. Click on <i>Add</i> and add the <i>Azure Folder (Or the folder you created)</i>.</p> <p> <br></p> <p>     </p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://4.bp.blogspot.com/-w30Z265_l6U/WHDr5oTW2SI/AAAAAAAAElE/eAlLFs5tW8Mz2DLiQaTD3_0InFvoH3iuwCLcB/s1600/Code%2BSnippet%2B09.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://4.bp.blogspot.com/-w30Z265_l6U/WHDr5oTW2SI/AAAAAAAAElE/eAlLFs5tW8Mz2DLiQaTD3_0InFvoH3iuwCLcB/s400/Code%2BSnippet%2B09.png" border="0" width="400" height="298"></a></p> <p>     </p> <p>     9. Now the code snippet is available.</p> <p> <br></p> <p>     </p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://3.bp.blogspot.com/-ejhU_7MaRu0/WHDsII2OA8I/AAAAAAAAElI/NcnbK6GNiCIXaoQmZWFlfUdDpCC6u9f-ACLcB/s1600/Code%2BSnippet%2B08.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://3.bp.blogspot.com/-ejhU_7MaRu0/WHDsII2OA8I/AAAAAAAAElI/NcnbK6GNiCIXaoQmZWFlfUdDpCC6u9f-ACLcB/s640/Code%2BSnippet%2B08.png" border="0" width="640" height="210"></a></p> <p>  </p> <header><!--data-blogger-escaped-<title> - Name of the snippet.</p> <p style="text-align: left;">         ii. <header><description> - Description of the snippet.</p> <p style="text-align: left;">         iii. <header><author> - Your name</p> <p style="text-align: left;"> <br></p> <p style="text-align: left;">         This what I have done.</p> <p style="text-align: left;"> <br></p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://3.bp.blogspot.com/-eDN9dJcXlqU/WHCLDx2OynI/AAAAAAAAEkQ/2CDXsMpsdA8zo1XjxUtGLTz_oBkpXw9VgCLcB/s1600/Code%2BSnippet%2B06.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://3.bp.blogspot.com/-eDN9dJcXlqU/WHCLDx2OynI/AAAAAAAAEkQ/2CDXsMpsdA8zo1XjxUtGLTz_oBkpXw9VgCLcB/s640/Code%2BSnippet%2B06.png" border="0" width="640" height="360"></a></p> <p style="text-align: left;">     </p> <p style="text-align: left;">     6. Scroll-down and change the <i>Snippet Section.</i>  </p> <p style="text-align: left;">         i. Add all parameters required as <declaration><literal></p> <p style="text-align: left;">         ii. Add the code in <code> node.</p> <p style="text-align: left;"> <br></p> <p style="text-align: left;">         See the way I have added the <i>sp_set_firewall_rule</i> stored procedure. You can see, I have added three <literal> nodes for handling three parameters and have configured <i>Name (ID), Tooltip </i>and <i>Default</i> value.</p> <p style="text-align: left;"> <br></p> <p style="text-align: left;">     </p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://1.bp.blogspot.com/-kXr9-bAVYmo/WHCMiEg7_9I/AAAAAAAAEkc/7r2FIBQQ1r8Piq8hG9koEqG0oQYPNs-aACLcB/s1600/Code%2BSnippet%2B07.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://1.bp.blogspot.com/-kXr9-bAVYmo/WHCMiEg7_9I/AAAAAAAAEkc/7r2FIBQQ1r8Piq8hG9koEqG0oQYPNs-aACLcB/s640/Code%2BSnippet%2B07.png" border="0" width="640" height="410"></a></p> <p style="text-align: left;">     </p> <p style="text-align: left;">     7. Now you can use the Snippet Shortcut when you need to set a Azure Firewall Rule.</p> <p style="text-align: left;"> <br></p> <p style="text-align: justify;">     </p> <p class="separator" style="text-align: center; clear: both;"> <a imageanchor="1" href="https://4.bp.blogspot.com/-PT0E40pcG_Q/WHCNGhdYsuI/AAAAAAAAEkk/dchull844FMCTMHhu-aE7emIS_1rqM6SQCLcB/s1600/Code%2BSnippet%2B08.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://4.bp.blogspot.com/-PT0E40pcG_Q/WHCNGhdYsuI/AAAAAAAAEkk/dchull844FMCTMHhu-aE7emIS_1rqM6SQCLcB/s640/Code%2BSnippet%2B08.png" border="0" width="640" height="210"></a></p> </div> <div> <p style="text-align: justify;">     </p> </div> </div> </div> -->

No comments: