In my last quick tip, I wrote about SQL 2012 Code Snippets in the intelliSense menu and how easy they are to use. What I didn’t mention is that you also have the ability to create custom code snippets. Why would you want to do this? The biggest reason is to create consistency in development of these types of objects across your environment. What if your company has a specific header or code block format for stored procedures that they have a hard time enforcing because it is a pain to keep cutting and pasting for developers. Wouldn’t it be easier for a developer to right-click and add a snippet that already has all the headers and formats included? It would be very easy to create a central repository of custom code snippets for all developers in your shop to use when building new objects.
So how do we do this? In this case, you will need to open the Code Snippets Manager. To do this, go to Tools -> Code Snippets Manager… or Ctrl+K, Ctrl+B.
This will open up all of the Code Snippets that come canned in SQL 2012. What is important to recognize is that all of these code snippets are actually xml files. In this example, I am going to create a custom code snippet for a stored procedure with a comment block at the top. The easiest way is to just copy the canned ‘Create Procedure Basic Template’ and modify it. From the image below, you can see I identified where the xml files currently reside.
I created a folder called Custom_Snippets on my C: Drive, copied the xml file and renamed it ‘SP_wCmntBlk.snippet’.Once that xml file was copied to my new folder, I can open it with Visual Studio or notepad update it to look like this.
Once I finish updating the xml code, I save it. Then, back in the Code Snippets Manager, I click Add, drill down to the folder I created called Custom_Snippets and click Select Folder. This folder will now show up in the Code Snippets Manager. In fact, any .snippet file you place in the folder will now automatically be visable.
Now if I open up SSMS, Open a New Query, right-click in the query editor and click Insert Snippet, I will see my Custom Snippet folder and can select the snippet I just created.
You don’t need to be an expert with xml to do this as I just showed you. It is rather simple and can be very useful. Try it our and let me know what your thoughts are!