Product articles
SQL Prompt
SQL Code Snippets
Customizing the SQL Prompt built-in…

3 February 2018

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.

3 February 2018

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

Phil Factor improves SQL Prompt's built-in ALTER TABLE ADD (ata) snippet so that it enforces certain coding standards, such as specifying whether the column accepts NULL values, and ensuring the new column is well-documented.

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.

/**
Summary: >
  Add the column <Name of the new column, sysname, MyColumn> of datatype <datatype of this column,, datetime> with the option of <NULL or NOT NULL (allow nulls?),, NULL>
  to the table <Name of the Schema, sysname, dbo>.<Table name, sysname, $SELECTEDTEXT$> on <database, sysname, $DBNAME$> (<What is this column for?, sysname, unknown>)
Author: $USER$
Date: $DATE$
Database: <database, sysname, $DBNAME$>
**/
USE <database, sysname, $DBNAME$>
GO

-- Add a new column to the table
ALTER TABLE <Name of the Schema, sysname, dbo>.<Table name, sysname, $SELECTEDTEXT$>
    ADD <Name of the new column, sysname, MyColumn> <datatype of this column,, datetime> <NULL or NOT NULL (allow nulls?),, NULL> -- <What is this column for?, sysname, unknown>
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'<What is this column for?, sysname, unknown>', 
@level0type = N'SCHEMA', @level0name = <Name of the Schema, sysname, dbo>, 
@level1type = N'TABLE',  @level1name = <Table name, sysname, $SELECTEDTEXT$>,
@level2type = N'COLUMN', @level2name = <Name of the new column, sysname, MyColumn>;

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.

/**
/**
Summary: >
  Add the column MobileNumber of datatype VARCHAR(14) with the option of NULL
  to the table Sales.Customer on AdventureWorks2012 (Contains the mobile number of the customer)
Author: PhilFactor
Date: 01/02/2018
Database: AdventureWorks2012
**/
USE AdventureWorks2012
GO

-- Add a new column to the table
ALTER TABLE Sales.Customer
   ADD MobileNumber VARCHAR(14) NULL -- Contains the mobile number of the customer
GO
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'Contains the mobile number of the customer', 
@level0type = N'SCHEMA', @level0name = Sales, 
@level1type = N'TABLE',  @level1name = Customer,
@level2type = N'COLUMN', @level2name = MobileNumber;

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:

/* ALTER TABLE my_Table ADD my_Column INT (NULL or NOT NULL) CONSTRAINT my_Column_key (UNIQUE or PRIMARY KEY) REFERENCES Another_table(key_column) CONSTRAINT my_Column_Default DEFAULT 0 WITH VALUES */

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.

Share this post.

You may also like

  • Article

    Problems Caused by Use of the SQL_VARIANT Datatype

    Phil Factor illustrates the 'quirks' of the SQL_VARIANT datatype and why it's best to investigate when SQL Prompt alerts you to its use. It is only safe to store data as a SQL_VARIANT, if you explicitly convert it to its true type before you use it.

  • Article

    The risks of using EXECUTE ('SQL Script')

    SQL Prompt’s code analysis rule, BP013, will alert you to use of Execute(string) to execute a batch in a string, often assembled dynamically from user input. This technique is dangerous because the parameter values are injected before the statement is parsed by SQL Server, allowing an attacker to "tag on" extra statements. Use sp_ExecuteSql instead, and validate the string inputs.

  • Article

    Quick SQL Prompt tip – why you should expand the wildcard

    One of the great things about SQL Prompt is that it quickly removes the need to use so many keystrokes. That’s helpful and handy, but to become a really efficient T-SQL coder, you’ll want to practice incorporating a few tricks into your routine. Here’s a good one. Often I run into tables and can’t remember

  • Article

    Quick SQL Prompt tip – using the CDB 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 ‘cdb’ snippet that helps with quickly creating a new database for development work. This can also be used to ensure production