BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
With SQL Server 2012 came the power not just to use pre-defined code snippets, but also to create your own - this blog explains how.
- Creating your own snippets of SQL
- Three examples of the use of snippets (this blog)
- Creating custom snippets using Snippets Generator
- Importing SQL Snippets into Management Studio
Posted by Andy Brown on 23 November 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Three examples of the use of snippets
So why would you create your own snippets in SQL? This pages gives three ideas.
Example 1: imposing a convention for stored procedure comments
If you want to ensure that everyone in your company follows the same rules for creating a stored procedure, you could create a comments snippet:

Right-click and choose the option shown.
You can also press CTRL + K followed by CTRL + X to insert a snippet.
You can now choose which snippet you want to add:

Here I've put all my snippets in a separate folder.
You could then choose your snippet to add stored procedure comments:

Choose this snippet to add comments before your stored procedure.
Lo and behold:

Now all you have to do is to fill in your name and the date you last updated this procedure.
Example 2: automatically adding standard joins
How many times have you joined the same tables together? To avoid reinventing the same wheel time and time again, put your joins in a snippet:

Here you can add joins just by inserting a snippet.
You can now delete any joins that you don't need for this query:

The trick is to include every join in your snippet, then comment out or delete the ones you don't need for your current query.
Example 3: putting selected code in a comment block
I think this is my favourite example. Having written a block of code, you will often want to make it stand out, for example by turning this:
DECLARE @answer int = 42
DECLARE @question varchar(max) = 'Trump'
Into this:
-- CHANGE MADE ON dd/mm/yyyy BY xxx
DECLARE @answer int = 42
DECLARE @question varchar(max) = 'Trump'
-----------------------------------
Having inserted the SurroundText snippet, you could then edit the comment to say anything you wanted. To do this, you need to include a different type of snippet:

Choose one of the surround snippets that you've written (here we've only created one):

You can see that we've set a description for the snippet too, which appears as the tool tip (here it begins with the word Puts, but I've truncated the rest of the screen shot).
Time now to tidy up:

I'd indented my comments within the surround snippet, which in retrospect was probably a mistake.
Having looked then at what you can do with custom snippets, the next part of this blog shows how to create them.
- Creating your own snippets of SQL
- Three examples of the use of snippets (this blog)
- Creating custom snippets using Snippets Generator
- Importing SQL Snippets into Management Studio