3 February 2018
3 February 2018

Customizing the SQL Prompt built-in snippets: a better ALTER TABLE ADD (ata) snippet

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.

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane.

SQL Prompt also comes with many useful built-in snippets, but sometimes we need to do some customization work, to add the functionality we need. As an example, how might we improve the ALTER TABLE ADD snippet, ‘ata‘?

The built-in ata snippet

Open the Snippet Manager from the SQL Prompt menu, and click on the ata snippet to see its code.

C:\Users\TONY~1.DAV\AppData\Local\Temp\SNAGHTML1b19178.PNG

This snippet seems designed more to reassure you with its simplicity than to impress you with its versatility. It contains two custom placeholders, $table_name$ and $column_name$, and two built-in placeholders $SELECTIONEND$ and $SELECTIONSTART$, to specify that the INT should be selected after the snippet is applied.

It gets you started, at least, but it doesn’t get you very far. Does the new column accept NULL values? Should it have DEFAULT value? A CHECK constraint, or a UNIQUE constraint? A COLLATION specification? How do we document the purpose of the new column? All of this is still manual work.

Of course, one can end up over-complicating the snippet to cover all possible cases. As a DBA, you will just want the snippet to enforce certain minimum standards, such as specifying whether the column accepts NULLs, and making sure the new column is well-commented and documented.

Customizing snippets using template parameters

It is a well-kept secret that you can use template parameters in snippets. SQL Prompt will call the Specify Values for Template Parameters function in SSMS, and when you invoke the snippet you’re presented with a nice SSMS form for specifying all the template parameter values.

When you’ve finished, the snippet will be pasted into the query window with everything filled in using the details you supplied in the form. You can even use SSMS templates directly as snippets if you were to add them to your snippet collection, because the parameters work as they always did.

The template parameters are enclosed in angle brackets (<>) within the snippet, in the format:

<parameter_name, data_type, default_value>

As far as I’m aware the data_type information is never used and can be safely left blank. The parameter_name can be a complete sentence that explain what you expect to be entered for that parameter. The default_value can itself contain a built-in placeholder. You can use the same parameter value in several places in the snippet, but the entire parameter block, within the <> delimiters, must be identical, otherwise it will be repeated several times in the SSMS form.

This allows us to be a lot more versatile. Listing 1 shows the code for my custom ata snippet.

Listing 1

Having edited the existing ata snippet to the new definition, we can write the name of a table into a query pane (or drag it in from SSMS Object Explorer), select it and then invoke the new ata snippet from the Actions list. You’ll be presented with the SSMS form to fill in all the parameter values.

Notice that use of the $DBNAME$ placeholder has filled in the database name, and the $SELECTEDTEXT$ built-in placeholder has captured our highlighted text as the table name. In this example, we had added the schema name but we have cut and paste that into the Name of the Schema value.

You can use other information such as the name of the user, or whatever is on the clipboard. You can now fill in the form with the correct details.

Then, just hit the OK button.

Listing 2

Not only do we have the code to add the extra column but, we also have in place, automatically, the rather unpleasant code to add the documentation for the column. This is also added to the source header. My name and the date has been added too, as if by magic(well via the built-in placeholders, actually).

As discussed earlier, we could take this snippet further, to account for the need to add several columns at once, add a column CHECK constraint, or a DEFAULT constraint, and so on. However, I see little sense in trying to complicate a snippet beyond the obvious, so maybe the best approach is to add a clue of the syntax, such as:

If you enclose these syntax hints with the placeholders $SELECTIONSTART$ and $SELECTIONEND$, then you can quickly delete them once you’ve reminded yourself of the syntax. Although I can keep the common syntax in my head, there is a huge amount that I have to look up so any cribs that one can get from a snippet would be useful.

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

    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

  • Article

    Building reusable table build scripts using SQL Prompt

    You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Consider the following AdventureWorks query. SELECT * FROM Sales.Customer INNER JOIN Person.Person

  • Article

    Avoid use of the MONEY and SMALLMONEY datatypes (BP022)

    The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify monetary values preceded by a currency symbol, but SQL Server doesn’t store any currency information at all with the actual

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use

  • 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