Using Parameters in #SQLPrompt

I am a big fan of snippets in SQL Prompt, often using them in demos to quickly get code written. However, I’ve liked the idea of snippets and templates for a long time. These are great time savers, and they can dramatically improve productivity and code quality.

How? If you have certain constructs in your environment that developers struggle to remember or implement, make a snippet. This makes things very easy and consistent. It’s a great way to help younger developers learn as well.

Here’s an example.

Create Primary Keys

I have worked with no shortage of developers that build tables like this:

CREATE TABLE Shipper
( ShipperKey INT NOT NULL
, ShipperName VARCHAR(100)
, ShipperAddress VARCHAR(100)
, ShipperCity VARCHAR(100)
, ShipperRegion VARCHAR(20)
, CountryCode CHAR(3)
)
GO

This isn’t a great design, but more importantly, deploying this results in a heap. Perhaps another issue is that there are no indexes, which isn’t usually a good idea.

A better idea might be a table like this:

CREATE TABLE dbo.Shipper
( ShipperKey INT NOT NULL CONSTRAINT ShipperPK PRIMARY KEY
, ShipperName VARCHAR(100)
, ShipperAddress VARCHAR(100)
, ShipperCity VARCHAR(100)
, ShipperRegion VARCHAR(20)
, CountryCode CHAR(3)
)
GO
CREATE INDEX Shipper_Region ON dbo.Shipper (ShipperRegion)

Now we can’t template all of this, but we can do a few things. I’ll show you how Prompt facilitates this.

A Customized Snippet

Let’s start with the basic code. I know I need a table name, I’ll want a PK, and I want to help someone add at least one index. With that in mind, I’ll build this snippet code. Note that I’ve replaced the table name with a parameter. I did this with a search and replace in the script.

CREATE TABLE dbo.$TableName$
( $TableName$Key INT NOT NULL CONSTRAINT $TableName$PK PRIMARY KEY
   $CURSOR$
)
GO
CREATE INDEX $TableName$_ ON dbo.$TableName$ ()

You can see this in the SQL Prompt Snippet Manager. Note that the parameter (or placeholder) has been inserted in the bottom by Prompt.

2019-12-10 21_07_39-SQL Prompt - Create New Snippet

One other thing I might do is add a schema placeholder like this:

2019-12-10 21_08_35-SQL Prompt - Create New Snippet

Note that I’ve added a default for schema, as this is usually dbo. I’ll also click the up arrow to the right to ensure schema is entered first.

Now, let’s use this. I’ll save this and close the options. Then in a new query window, I’ll type my snippet beginning as “crt”. I see this:

2019-12-10 21_09_41-CandidateList

My snippet is listed. I can select it and I’ll then see this code. See how Prompt has inserted my snippet, but already highlighted the schemaname parameter and given me the intellisense of the schemas in my database.

2019-12-10 21_11_28-SQLQuery4.sql - Plato_SQL2017.sandbox (PLATO_Steve (64))_ - Microsoft SQL Server

I’ll type dbo and Enter. Prompt moves to the next placeholder parameter. Here I see TableName highlighted all over.

2019-12-10 21_12_31-SQLQuery4.sql - Plato_SQL2017.sandbox (PLATO_Steve (64))_ - Microsoft SQL Server

If I type “Shipper”, I see this.

2019-12-10 21_12_40-SQLQuery4.sql - Plato_SQL2017.sandbox (PLATO_Steve (64))_ - Microsoft SQL Server

Now I’ll hit Enter again. This time Prompt puts the cursor where I need it to start entering other columns.

2019-12-10 21_13_45-SQLQuery4.sql - Plato_SQL2017.sandbox (PLATO_Steve (64))_ - Microsoft SQL Server

I can easily enter my columns, and now my table has a PK. What’s more, if I enter a few columns and run this, I’ll see an error.

2019-12-10 21_14_33-SQLQuery4.sql - Plato_SQL2017.sandbox (PLATO_Steve (64))_ - Microsoft SQL Server

The table was created, but the index statement isn’t correct. While this doesn’t necessarily ensure developers follow naming standards or create an index, at least this will get them to think about it. They can correct the statement by adding a column to the index statement between parenthesis, and hopefully change the name.

If you haven’t seen how Prompt can really improve your coding, download an eval today and give it a try. If you have it, take advantage of snippets.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.