Create your own snippets of SQL code, and insert them into your queries
Part two of a four-part series of blogs

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.

  1. Creating your own snippets of SQL
  2. Three examples of the use of snippets (this blog)
  3. Creating custom snippets using Snippets Generator
  4. 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:

Inserting a 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:

Wise Owl snippets

Here I've put all my snippets in a separate folder.

You could then choose your snippet to add stored procedure comments:

Choosing stored procedure comments snippet

Choose this snippet to add comments before your stored procedure.

Lo and behold:

Stored procedure comments added

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:

Joins 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:

Added joins

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:

Surround with snippet
 

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

Choose the surround snippet

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:

Tidying up snippet

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.

This blog has 0 threads Add post