20 April 2012

Using a SQL Prompt snippet with template parameters

As part of my product management role I regularly attend trade shows and man the Red Gate booth in the vendor exhibition hall. Amongst other things this involves giving product demos to customers. Our latest demo involves SQL Source Control and SQL Test in a continuous integration environment.

In order to demonstrate quite how easy it is to set up our tools from scratch we start the demo by creating an entirely new database to link to source control, using an individual database name for each conference attendee. In SQL Server Management Studio this can be done either by selecting New Database from the Object Explorer or by executing “CREATE DATABASE DemoDB_John” in a query window.

We recently extended the demo to include SQL Test. This uses an open source SQL Server unit testing framework called tSQLt (www.tsqlt.org), which has a CLR object that requires EXTERNAL_ACCESS to be set as follows:

This isn’t hard to do, but if you’re giving demo after demo, this two-step process soon becomes tedious.

This is where SQL Prompt snippets come into their own.

CreateSnippetContextMenu_thumb_3.png

I can create a snippet named create_demo_db for this following:

Now I just have to type the first few characters of the snippet name, select the snippet from SQL Prompt’s candidate list, and execute the code.

Simple!

The problem is that this can only work once due to the hard-coded database name. Luckily I can leverage a nice feature in SQL Server Management Studio called Template Parameters.

If I modify my snippet to be:

Once I’ve invoked the snippet, I can press Ctrl-Shift-M, which calls up the Specify Values for Template Parameters dialog, where I can type in my database name just once.

TemplateParams_thumb_3.png

Now you can click OK and run the query. Easy.

Ideally I’d like for SQL Prompt to auto-invoke the Template Parameter dialog for all snippets where it detects the angled bracket syntax, but typing in the keyboard shortcut is a small price to pay for the time savings.

On April 24th (Europe) and May 1st (North America), Red Gate is hosting a free educational webinar covering existing and new features in SQL Prompt, including the new and exciting “experimental features”. Sign up here: http://www.red-gate.com/products/sql-development/sql-prompt/webinars

test

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 3849 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

David Atkinson

View all articles by David Atkinson

Related articles

Also in Blogs

Azure SQL Data Warehouse Lives!

Frequently when a new piece of tech that I’m excited about is launched, total nerd that I am, I’ll start quoting Colin Clive in, still the best, James Wale’s Frankenstein. It’s ALIVE! ALIVE! Well, time to get excited. On Monday, July 11, Azure SQL Data Warehouse moves from being in preview on Azure to a … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening