Shortcut: Using "surrounds with" snippets in SQL Server Management Studio

In previous posts, I’ve been talking about how to use snippets in SQL Server Management Studio (SSMS) and how to create your own. There are several types of snippets and one of the special types of snippets that I want to mention are the “surround with” snippets.

If you look at the following block of code:

Imagine that you want to execute the four highlighted lines only when a condition is true. If I hit Ctrl-K and Ctrl-S while they are highlighted, I’m prompted with this:

Note that I get an option to surround them with a BEGIN/END, or an IF or WHILE. Let’s choose an IF. I just double-click the If option and this happens:

We can then just type the condition. Notice that because I was using multiple lines, it put them all in a BEGIN/END block for me.

This is all quite good but I still might want to create my own instead, and I can do that. An example of why I might want to do that, is that I might want a statement terminator after the END.

If we go into the Code Snippets Manager (from the Tools menu), and expand the Function category, we can see this:

I can’t say that I think of IF as a function but none-the-less, note that the type is a SurroundsWith snippet. Let’s see how it’s defined. The Location is here:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SQL\Snippets\1033\Function\If.snippet

If I open that file in NotePad++, I see the following. Note how the value called $selected$ is enclosed within the snippet.

I could then either modify this one (probably not best), or use it as the basis of a new snippet for myself.

2018-08-02