17 January 2018
17 January 2018

How to use the SQL Prompt snippet placeholders for selecting and copying text

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

There are four SQL Prompt snippet placeholders that are all about selection and copying of text:

$PASTE$ Inserts the contents of the clipboard at that position.
$SELECTEDTEXT$ Inserts the selected text.
$SELECTIONSTART$ Indicates where you want the start of the new selection of code after you have executed the snippet
$SELECTIONEND$ Specifies the end of the new selection of code after you have executed the snippet

These placeholders are very handy if, for example, you have two blocks of code that need to be part of a snippet. One part you hold on the clipboard, and the other part is a block of snippet text, selected using the $SELECTEDTEXT$ placeholder.

The easiest way of demonstrating how to use them is with some practical examples.

Saving metadata queries as views and TVFs

Imagine that you have a number of queries that you need to turn into inline table-valued functions (iTVFs). I don’t have to imagine too hard because I have pages of metadata queries that I use for exploring the contents of databases.

They are fine as queries, when you don’t want to mess up a database with your code, but during database development work, it is far better to have a schema with all your development code handy. To tidy up at the end, all I’ll need to do is delete the development schema.

The obvious way of storing these sorts of queries is as views, or as iTVFs, if they require parameters. To do this, I’ll create a couple of snippets, ToView and ToTVF. Broadly, they both work like this:

  1. Use Ctrl-C to copy the name of the view or TVF, from the corresponding query, to the clipboard
  2. Highlight the query and run the relevant snippet, which will capture the selected code into the $SELECTEDTEXT$ placeholder in the snippet
  3. Use of the $SELECTIONSTART$ and $SELECTIONEND$ placeholders, in the snippet, will mean that the resulting view or TVF is highlighted and ready to execute, just by pressing F5.

I’ll spin up a copy of AdventureWorks to demonstrate.

Create the snippets

Listing 1 shows the template code for the ToView snippet. The name of the view will be held on the clipboard and used in the $PASTE$ placeholder. I’ve hardcoded the schema name (utils), but you could always use a placeholder for this too.

Notice that I’ve put a $SELECTIONSTART$ at the beginning of the template and a $SELECTIONEND$ at the end, which means that after invoking the snippet, the whole of the substituted text including the test execution will be highlighted. I’ll be able to compile and run the iTVF immediately, by clicking on ‘execute’ or hitting F5.

Listing 1: The ToView snippet

Highlight the code in SSMS, right-click, select Create Snippet, call it ToView, give it a description, and save it.

Then, do likewise for the ToTVF snippet, which is similar and is shown in Listing 2.

Listing 2: The ToTVF Prompt Snippet

If we open the Snippet Manager from the SQL Prompt menu, we see they are now created and saved.

Figure 1: The saved ToTVF and ToView snippets

Turn a query into a view or iTVF

We go to the query pane where we have the code, select the name we want to use for the object and hit Ctrl-C to copy it on the clipboard.

Figure 2: Copy the name for the view, from the query

We now have the name we want for the view, held on the clipboard. Next, make sure you’re connected to the right database, then highlight the query itself, and invoke the relevant snippet, in this case ToView, from the SQL Prompt Action list.

Figure 3: Executing the ToView snippet

We double-click on the name of our snippet to execute it and…now we have the query wrapped up in a view, with everything selected that we need to create and test it.

Figure 4: The code to create and test the view, ready to execute

Just hit F5, or click on ‘Execute’, and we should get this satisfying result.

Figure 5: Executing the TheTables view

A view has been compiled and tested with very little effort. Of course, I could have then clicked (Ctrl K, Ctrl Y) to format it all nicely!

Building a query from SSMS Object Browser

As another example, imagine you are exploring a database and want to work on a table. We need a handy SELECT statement to start exploring that table, and its data. Figure 6 shows a very simple ToQuery snippet that we can use.

Figure 6: The ToQuery snippet

Now, click on the the table of interest, in SSMS object browser, and drag the Columns folder into the query pane, to get a list of all the columns. Then, onto the next line, drag over the table name.

Figure 7: Build the query by drag-and-drop

Highlight the table name, Person.Address, and hit Ctrl-X to cut it, and also capture it onto the clipboard. Then hit Ctrl-A to highlight the remaining column list and invoke the ToQuery snippet from the Actions list.

Figure 8: Execute the ToQuery snippet

Use Ctrl+K, Ctrl+Y (or SQL Prompt | Format SQL) to format the code using the current active style, and then hit F5 or the Execute button.

Figure 9: Results of executing the query

So, we now have another quick way to build a SELECT statement for a table, all correct and nicely formatted. We can develop this into exactly what we need to explore one or more tables.

Summary

I’ve demonstrated just a couple of ways of using these four useful snippet default placeholders. Once you’ve used them as few times, all sorts of other uses will occur to you, so perhaps it is best if I leave it there!

For more detail about how SQL Prompt Snippets and templates work, and how they compare to the standard SSMS versions, check out my earlier article: https://www.red-gate.com/hub/product-learning/sql-prompt/templates-and-snippets-in-ssms-and-sql-prompt.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement

    Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order. In a SELECT statement, you

  • Article

    SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

    The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() window clause instead, if you need to impose a particular order on rows in the

  • Article

    Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)

    SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command: PE008 – SET NOCOUNT OFF is used PE009 – No SET NOCOUNT ON before DML Whenever you execute a query, a short message is returned to the client with the number of rows that are affected

  • Article

    Quick SQL Prompt tip – using the ii snippet

    SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the “ii” snippet that helps with inserting data into a table. If I type “ii” and hit tab, I quickly get the

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt.

    As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly