Phil Factor

3 February 2018

• SQL Prompt
• SQL Code Snippets

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

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.

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.