• SQL Prompt
• SQL Code Snippets

Creating a SQL Code Snippet in SQL Prompt and SSMS

When SQL Server Management Studio was created, it came with templates, which are used within SSMS for the ‘New’ menu and can be accessed (via drag/drop or click) from the template browser. Because they are maintained as part of the SSMS product, they are very useful for getting you started with any SQL-based operation; the Template browser is like an encyclopedia of SQL Code.

Snippets aren’t there to try to supplant templates. They are designed to be easily configurable chunks of code that the individual developers or teams can fine-tune to their requirements. After all, you would never want to add entries into an existing, printed Encyclopedia, but you would want to do so into your notepad.

No two database developers ever agree on matters of coding, such as how code should be laid out, what constitutes best practice, and will therefore never agree on which code snippets will be useful. Therefore, SQL Prompt has developed over the years to make as much as possible configurable and changeable. It provides a simple set of built-in snippets, which illustrate how to use, create, or alter them, and then expects users to adapt them, and add their own custom snippets, according to the way that they work, and the tasks that they perform.

Let’s start with using SQL Prompt to create a snippet, but I wouldn’t want anyone else to miss out, so I’ll show you how to do it in SSMS as well.

What are Snippets?

Snippets are small pieces of reusable code that can be quickly inserted in your script, while editing. This snippet of code can come in a various form, such as a full-fledged function, a batch of code or a simple single line statement. Snippets can also come in various languages such VBScript, PowerShell, C#, or SQL. These snippets are stored in a standard XML format that is compatible with Microsoft Visual Studio, so you can painlessly import your existing snippets into other add-ins or VS products. Most IDEs and Code editors use the same XML format (http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet).

A test harness for getting timings for your queries and batches

About 3 years ago, I wrote a timings snippet for SQL Prompt, which was recently added to its collection of built-in snippets. It wraps a test harness around selected code and is useful when you need to get execution times for several sections of code in a long batch or test the performance of several variants of a routine. It captures the timing for each section, saves it in a table then reports all the results, so I get a better idea of what it is that is taking all the time.

Often though, I just need a simple and quick way to test the performance of a single routine or algorithm, repeatedly, as I refine it. There are two ways of doing this, so we can try both.

If you really aren’t bothered about getting hold of the result in SSMS to do analysis, you can use SET STATISTICS TIME:

It is possible to read these message statements as data, from a script, but for this demonstration we just admire the messages pane.

routine for listing Constraints
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.

I’ve switched NOCOUNT ON and I’ve added a PRINT statement so you can be sure what the figures relate to.

The other way of doing it is to use a variable and this has the advantage of allowing you to store the result for graphs, or whatever else you wish (my timings snippet uses a table variable to get a complete report, but this makes it more complicated than you sometimes want, if you’re just measuring one or two parts of your code).

Note that you have a lot more versatility here. You can select the units of time, such as seconds, milliseconds or whatever, or merely record the time interval in mins:secs:ms, as in the next example.

The other day I found myself using the simpler test harness rather a lot, on a troublesome search algorithm. Such were the feelings of ‘déjà vu’ at times, that I felt compelled to make a snippet to do it.

Basically, I wanted a snippet that wraps the test harness around whatever code you select in the SSMS query pane. In SSMS, this is called a ‘Surrounds with’ snippet.

Creating the SQL Prompt snippet

There are several ways to create a SQL Prompt snippet. To create one from existing text in a query pane, just highlight the text, and select Create Snippet, either from Prompt’s Action menu (the red speech bubble that appears) or from the right-click context menu.

Here, though we’ll create a new snippet from scratch, so we open the SQL Prompt snippet manager (SQL Prompt – Snippet Manager) and hit the New… button.

This takes us to the snippet editor where we enter the code for our test harness, which, on invoking the snippet, will surround the code we wish to time.

Give the snippet a name (I chose took) and description and hit Save.

Built-in and custom placeholders

The took snippet uses the $SELECTEDTEXT$ built-in variable as a “placeholder” for the code we wish to test. When we select that code, in an SSMS query pane, and invoke the snippet, SQL prompt will inject the text you selected into that point in the snippet:

You’ll have noticed that I’ve also created two extra variables. When you invoke the snippet, these custom placeholders allow you to edit strings that exist in several places within the code, updating them all at once. It also allows you to tab between them. The timingVariable placeholder is there to make it easier to change the name of the variable, if you have more than one of these in the same batch and you want to select them all at the end. The explanation placeholder is just to remind you that you can change the column name to give each time a unique and explanatory name.

There are quite a few more built-in placeholders that you can use, beyond $SELECTEDTEXT$:

• $CURSOR$ – Specifies where the text insertion cursor position should be after the snippet has been inserted. For example,
  SELECT OBJECT_DEFINITION (OBJECT_ID('$CURSOR$')) AS ObjectDefinition;
  places the cursor after OBJECT_ID so that you can then type in the name of the function, view or procedure.
• $DATE$ – Inserts the current date in the format ‘dd/MM/yyyy’. You can specify the date in another format if you wish by specifying a custom date format using the standard .NET convention , for example: $DATE(dddd, d MMMM yyyy)$ gives Monday, 5 October 2020
• $DBNAME$ – Inserts the name of the database to which the query window is connected.
• $GUID$ – Inserts a globally unique identifier.
• $MACHINE$ – Inserts the name of the workstation or other machine that is running SQL Prompt.
• $PASTE$ – Inserts the contents of the clipboard at the indicated point in the snippet.
• $SELECTEDTEXT$ – The position in the snippet to insert the text that was selected when the snippet was invoked. You can use this placeholder to create any ‘surround with’ snippet that encloses your selection in a block, e.g. BEGIN…END or WHILE()…DO (for example Prompt’s be snippet, short for BEGIN…END, uses the $SELECTEDTEXT$ placeholder in this way).
• $SELECTIONSTART$ – This indicates where you want the start of the selected text block after the snippet is inserted. Used with $SELECTIONEND$
• $SELECTIONEND$ – This is used with $SELECTIONSTART$ to select a block of the snippet text. It indicates where you want the end of the selected text block after the snippet is inserted.
• $SERVER$ – Inserts the name of the connected SQL server.
• $TIME$ – This inserts the current time. You can specify a custom time format, for example $TIME(HH:mm:ss)$. Not much room for variety here but you can lose the leading zeros if that irritates you
• $USER$ – Inserts the name of the connected user.

See How to use the SQL Prompt snippet placeholders for selecting and copying text for examples of how some of these work.

Using SSMS template parameters in Prompt Snippets

You can use the contents of SSMS templates instead of, or as well as, Prompt’s custom placeholders. The advantage of doing so even if you just use the contents of an existing SSMS template, is that you get a form that you fill in for the values you need to enter.

Here is an example. Nobody keeps this sort of code in their heads. If you need to do a fair bit of this sort of work, just highlight the SSMS Template code and create a new snippet from it:

When you invoke the Prompt snippet, you get to see this.

In this ‘Specify Values for Template Parameters‘ dialog box, the Value column contains a suggested value for each parameter. Accept it or replace it with a new value. Yeah. No need to click open this dialog from the Query menu (or hit Ctrl+Shift+M): it just does it. All you then do is to click OK to close the dialog box to modify the script in the query editor with the values you’ve supplied.

Using a SQL Prompt snippet

I try to give each snippet a short 3-4 letter ‘shortcut’ for its name, and I’ll generally invoke the one I want simply by starting to type its shortcut, with the cursor positioned where I want the snippet code to be inserted into the query pane.

Alternatively, with any text highlighted, you can start typing the name of the snippet into the Action menu, as shown below. Only the ‘Surrounds with’ snippets, those that have a $SELECTEDTEXT$ placeholder, will be visible. SQL Prompt inserts the snippet code into the query pane, replacing the $SELECTEDTEXT$ placeholders within that code with the selected text.

You can also use the suggestion box – type Ctrl-space then, from the All Suggestions drop down, select Snippets to select just the snippets in the suggestions.

Saving and sharing snippets

Each SQL Prompt snippet is saved in an XML file with the rather garrulous filetype of .sqlpromptsnippet. These files are kept in a single directory the location of which can be set by the user but defaults to (in PowerShell):

“$($env:HOMEDRIVE)$($env:HOMEpath)\AppData\Local\Red Gate\SQL Prompt 10\Snippets”

Here is the XML file for our timing snippet.

It is possible to create snippets in XML and add then to the directory, or edit them in the directory, but it isn’t necessary and it’s generally easier to open them in the UI and edit them there.

Creating and using an SSMS Snippet

Here is the snippet that we put into SQL Prompt. I’ve written it as a VS Snippet XML file (.snippet) but it is very easy to alter an existing Prompt snippet.

You’ll see that the general principles for creating an SSMS snippet are the same, but you need to create an XML file to prepare it to be imported. If there is an SSMS snippet editor, neither I nor Google has found it, but you can edit it on your favorite XML editor.

As with the SQL Prompt snippet, you use the TAB key to move from one replacement point to the next. As far as I know, the only two built-in placeholders, $selected$ and $end$.

Importing a snippet

After you’ve created a SQL snippet file, but before you can it use it in SSMS, it has to be registered using the Code Snippets Manager, accessed from the Tools menu (or CTRL+K, CTRL+B). From here you can view, import, add or remove snippets.

You use the ‘Add’ button to add a folder that contains several snippets or use the ‘Import’ button to validate and import one or more SQL snippets into a special user category folder called ‘My Code Snippets’. It isn’t possible to just add a snippet to the folder where they are stored.

I created a directory called SSMS, added the snippet to it, then used the Add button to add the directory. This way, I can edit or add snippets without having to (re)install them

Invoking an SSMS Snippet

There are several ways to invoke an SSMS snippet:

• Use the Edit menu (Edit> IntelliSense > Insert Snippet), or type Alt + E, I, I. If you type Alt + E, I, S you get a menu of the ‘surrounds with’ snippets
• Right-click the query window to display the context menu and choose either the Insert Snippet menu item or the Surround with menu item (if you have selected some text).
• Use the keyboard shortcut Ctrl+K, Ctrl+X for the snippet or Ctrl+K, Ctrl+S for the surrounds-with snippet

In all three cases the code snippet window will appear. Here I’m selecting a table function from the function submenu. The ‘tooltip’ isn’t helping much!

Here, I’m invoking the Begin ‘Surround with’ snippet.

Finally, here is our new took snippet that we just added.

Conclusions

Snippets are there for the code you would use more often, or more quickly, if it wasn’t for the effort of trying to recall it. Every developer has the urge to collect and hoard good queries as they ‘google’ around, and every developer will have different tastes and so different collections. Personally, I use snippets a lot for metadata queries of all sorts, especially if it is tedious to type in. SQL Prompt snippets are easy to create and maintain, and so are the obvious place to store them.

I recommend SSMS templates as the best point of reference for the enormous reservoir of five hundred and seventy-four SQL statements and queries that you need to use occasionally but for every-day work, snippets are better.

 

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more