Alasdair Daw

26 August 2015

SQL Prompt 7’s new snippet manager

During the development of SQL Prompt 7, we overhauled the snippet manager to make it more powerful and customizable. The snippet manager documentation lives here but there are some subtleties that I wanted to cover in in this post.

Everyone writes SQL differently and we want SQL Prompt to work with your current workflow rather than dictating it to you. Fully understanding the new snippet manager features will help you get more out of SQL Prompt.

What’s a snippet?

A snippet is a useful fragment of SQL that can be quickly inserted into a larger query. At their simplest, snippets are just abbreviations of commonly used, multi-keyword phrases such as ‘SELECT * FROM’ or ‘LEFT OUTER JOIN’.

Snippets are identified by name and this name is suggested just like a regular keyword while you’re typing. To insert the ‘SELECT * FROM’ snippet, type ‘ssf’ in a query window in SSMS or VS. The corresponding snippet will be suggested and can be inserted by pressing Enter (or another insertion key).

Snippets are great!

SQL Prompt comes with some default snippets but you can alter these or create your own from scratch.

Having a selection of useful snippets at your disposal when writing SQL means you can bypass the boilerplate and get on with the interesting stuff. Since you can easily copy snippets between SQL Prompt installations, you can encourage best practices and style conventions by sharing your snippets with your teammates.

To see the snippets that are available, in SSMS or VS, go to SQL Prompt > Options > Snippet Manager.

Placeholders

When creating a new database, you need to specify both the database name and the names of the data and log files. Generally these would all share the same name which, in a world without snippets, would mean either retyping or copying and pasting.

Since we do live in a world with snippets, in a query window you can type ‘cdb’ and the ‘Create database’ snippet will be suggested. The snippet will be inserted with the database name highlighted. The highlighted value is used five times in the statement and they’ll all be updated simultaneously as you name the database.

To see the structure of the placeholders within this snippet, open the snippet manager and find the ‘cdb’ snippet. Each position that requires the name property has the placeholder ‘$database_name$’.

Placeholder order

The above example had a single placeholder used multiple times. When there are multiple placeholders, you can change the order in which you’re prompted to fill them in.

In a query window, type ‘cdbsnap’ and insert the ‘Create database snapshot’ snippet with the first placeholder highlighted (the source database name). Pressing enter will highlight the next placeholder (the snapshot name).

If the idea of entering the database name before the source database name is completely offensive to you, it’s possible to change the order in which these placeholders are highlighted. In the snippet manager, find the ‘cdbsnap’ snippet and use the arrow buttons to change the order of the placeholders in the list.

Now when you insert this snippet, the first placeholder that you’re prompted to fill in is the database name.

Placeholder default values

Placeholders without a default value will use the placeholder’s name (as in the previous example). To see a default value in action, insert the ‘IF fragment’ snippet by typing ‘ifs’ and pressing Enter.

The first highlighted value in the inserted snippet is the boolean condition which has a default value. You can now type your own condition or press Enter to keep the default value and move to the next placeholder.

The default value for the condition is ‘1 = 1’. If you’d rather err on the side of caution when inserting code you could make the default false. To do this, open up the snippet manager, find the ‘ifs’ snippet and click Edit.

Change the default to value of the condition placeholder from ‘1 = 1’ to ‘1 = 0’.

Built in placeholders

So far we’ve only looked at custom placeholders which are basically free text fields. However, there are also built in placeholders that will populate themselves when they’re inserted.

To make use of these placeholders, we’re going to create a snippet that inserts a comment containing information about the SQL script. This could be useful for tracking who’s working on which objects when multiple people are developing against a shared database.

Open the snippet manager and create a new snippet called ‘tag’ and type this SQL into the snippet code area:

Try it out by typing ‘tag’ in a query window and inserting the snippet you just created.

To see all the built in placeholders, type ‘$’ in the snippet code area and the available placeholders will be suggested.

For more details on the built in placeholders and customizing the time and date format, see the documentation.

Placeholders as variables

It’s possible to access a built in placeholder again without it getting recalculated by storing it as a local variable. This can come in handy when reusing a GUID in a query.

For example, if you want to update related tables that have GUIDs as primary keys you can create a new snippet with this code:

Then set the default value of $ChangeId$ in the table to $GUID$.

When this snippet is inserted, the same GUID will shared by both the $ChangeId$ placeholders:

Surround existing SQL with a snippet

The examples used so far involve inserting the snippet body first, then adding the detail in afterwards. However, some snippets work better when they’re inserted around some SQL already in the query window.

In a query window, write some SQL, for example: EXEC sp_ExcitingProc

Select this text and the actions button will appear in the gutter:

Press Ctrl to expand the actions list and type ‘tc’ to find the ‘TRY .. CATCH fragment’.

Inserting this snippet will surround the highlighted text and place the cursor in the catch block of the statement.

Only snippets containing the $SELECTEDTEXT$ placeholder are included in the actions list (as this defines where the highlighted text is inserted). These snippets can still be inserted from the suggestions box.

Creating snippets from the query window

Snippets can also be created straight from the query window using the actions list so you can capture fleeting moments of SQL genius by turning them into a snippet as soon as they’re written. To do this, highlight the fragment of SQL that will form the snippet, press Ctrl and select Create snippet. This will automatically populate the Create New Snippet dialog.

What should our snippet system do that it doesn’t? What does it do that it shouldn’t? Is there a snippet that no SQL Prompt user should live without? We love getting feedback from users about SQL Prompt features, good or bad, so please get in touch via the SQL Prompt forum or Uservoice page.