Tag: Redgate

SQL Prompt is a fantastic coding aid, but it does more than format your code and provide intellisense. Over time, the team has enhanced SQL Prompt to also guide you along and fix some bad code that your team might write out of habit.

SQL Prompt 10.1 released recently, and one of the recent fixes is for an issue that we’ve denoted as ST003. This is an issue where a procedure is not enclosed in a Begin  End. Here’s an example.

I started writing this proc, which looks normal. I’m not sure what isn’t being qualified with the red squiggles, as the query runs fine along. In any case, I do have a green underline on CREATE PROCEDURE, which let’s me know of a style issue.

If I put the cursor on this line, I see the lightbulb in the left margin. Since this is available, I know I have a possible “fix” that SQL Prompt will handle.

If I click this lightbulb, I see a few options.

The top one is the one I want. When I click that, SQL Prompt will reformat my query and add the BEGIN and END.

This can be controversial, as the END doesn’t stop things from being included in the procedure. For example, if I do this:

I get this:

The CREATE PROCEDURE needs to have the batch end to stop including code. The END doesn’t do this. Make sure you have the habit of ending your procedure creation with the batch separator (GO by default).

If you haven’t tried SQL Prompt, download an eval and see what you think. If you have it, upgrade and ensure you have all the code fixes.

One of the most popular and widely used features of SQL Prompt is formatting code. The SQL Prompt extension is in public preview and you can get it here. I have another post that walks through the  installation.

Once it’s installed, you will likely want to use it, and one of the things you can do is format code. This post looks at a few of the basics for formatting code.

Quick Format

Let’s see how this works. First, we open ADS and choose a connection from the Server pane. I right click a database and choose “New Query”(shown here).

Next, I’ll enter the query here into the pane:

SELECT DISTINCT Name
FROM Production.Product WHERE ProductModelID IN (SELECT ProductModelID 
                                                 from Production.ProductModel
                                                 where Name LIKE 'Long-Sleeve Logo Jersey%');
GO

Once I have this, you can see in the image that the subquery has some  poor formatting. The subquery is quite wide and some keywords are cased differently.

Let’s CTRL+Shift+P to get the command palette. If I type format, I’ll see some options in the image below for formatting (or something similar).

I’ve been testing, so the recently used commands show the format document commands, which you will often use. In this case, I’ll select Format Document with. When I do that, I’ll get a list of formatters for SQL code installed on my system. In my case, I have SQL Prompt, the default mssql formatter, and a PostgreSQL formatter.

Once I select Prompt, formatting will begin. I see a note in the lower right corner of ADS.

When this is done, I can see my query is formatted appropriately (according to my style). The keywords are proper cased and the spacing for the subquery has changed.

Setting a Default Formatter

I don’t want a two step process every time I need to format code. In fact, the Format Document shortcut is Shift+Alt+F, and I want to use that. In order to do that, I’ll need to configure a default format.

Note: If you haven’t and you click Shift+Alt+F, ADS will ask you to configure one.

The command palette (CTRL+Shift+P) is the way to configure ADS. Click that and type “format”. You will see a list of commands, and you want to choose the “Format Document with” option. This brings up the list of formatters, as we saw above, but below this is the “Configure” option.

We choose this, and again see a list of formatters, but the text at the top has changed. In the image we can see this now says: “Select a default formatter for ‘SQL’ files”. We can pick SQL Prompt to set that.

Once we’ve done this, Shift+ALT+F will format your query.

Changing Styles

One of the things many of us want to do is use a custom formatting style. While the built in styles are handy, many users want custom ones. That includes me. I have a few custom styles I use regularly in SSMS. If you run ADS on Windows, your styles are imported. If not, I’ll do another post for that.

I can change styles in the command palette. Type “SQL Prompt” in there and you see options:

The one I’ll use is the recently used one “Change active formatting style” at the top. If I pick that, I see this on my ADS install:

There are a number of Built In styles that come with Prompt, but we can see my three custom ones have been imported from SSMS. Each time ADS starts, it imports your styles, so if you change on in SSMS, which has a better style editor, you will see it here when ADS reloads.

I will set my “way0utwest” style as active and then reformat the query. I now see this:

The parenthesis location has changed. I also see the notice in the lower right that appeared after selecting a new active style.

Summary

This is just a basic look at using the formatting and choosing a style. If you are on Windows, all of your styles are available. If not, you’ll have to use the built in ones or edit one for yourself. I’ll cover editing styles in another post.

I also have a video here of the process of formatting code: https://www.youtube.com/watch?v=EWCk7wUIL_s&feature=youtu.be