• SQL Prompt
• SQL Formatting and Styles

How to Apply Non-Standard SQL Formatting Using SQL Prompt

I use SQL Prompt to format my code; I’ve defined my standard style that lays out the code just the way I like it, for most cases. I also have a couple of alternative styles for specific tasks, such as comparing two versions of the same code.

Occasionally, however, the standard formatting just won’t work for certain code, or for certain sections of that code. So, like any good tool, SQL Prompt will not only format your code for you, but also “unformat” it for you, or allow you to selectively disable formatting for certain parts of it.

Unformatting code

Occasionally, you just want the code, or certain parts of the code, to be in a very condensed format, often simply because the formatted version takes up too many lines and becomes harder to read. For example, consider this simple query in Listing 1, for the WideWorldImporters database.

Listing 1

Clearly, I don’t want code like this, using *, in my production application, so I apply my SQL Prompt custom style using the Format SQL command (or the Ctrl K, Ctrl Y shortcut). I’ve also configured the Prompt Format > Styles options so that certain formatting actions run as part of this command, including Expand Wildcards and Insert Semicolons. I end up with a statement that will be 32 lines long, as you see in the Listing 2.

Listing 2

I’d like the SELECT list to be in a more condensed format, and for this I can use an interesting action called unformat. Highlight the entire statement in Listing 2, then from the Action menu, start typing in unformat and then select it from the filtered list.

When I first saw this command, I envisioned it being like an “undo” button, after formatting the code, but it’s not. It does not undo the effects of any refactoring actions you just applied, but it does remove all whitespace characters and replace with them with single space characters. The result is that it removes all pretty formatting from the code and puts every bit of the statement on a single line (although it may display as wrapped depending on where it appears, or on your SSMS settings).

Listing 3

It will preserve space around comments to make sure that a double hyphen (--) comment does not end up commenting out all the remaining code. You can use this trick occasionally to prevent Prompt making a line longer than the wrap (e.g. when you need to publish code online).

Of course, the more common need for unformatting is not for a complete statement, but just some part of it. Luckily, the unformat action works on any part of a statement or even comments if you just highlight the text of the comment. A place where I have used this quite a few times is when dealing with large lists of items, often when coupled with use of the Copy as IN clause feature.

Let’s say your users have asked for a query that will show them details of only those customers who have ordered more than 18000 dollars’ worth of items. Listing 4 will give you a long list of CustomerId values for those ‘high spending’ customers.

Listing 4

In the grid results, highlight the column, right-click, and choose Copy as IN clause, and you can quickly generate the query that your users need. However, it is almost 100 lines long.

Listing 5

To send this query to a user, you probably don’t want to apply your standard format, which might expand the wildcard, making the query even longer and, depending on the formatting style, indent the list, and so on. In which case, you can just highlight list of IDs, apply the unformat action, sprinkle in a few carriage return/line feed characters, and your query will look as shown in Listing 6.

Listing 6

Selectively disabling formatting

As wonderful as SQL Prompt is at formatting your code, sometimes, you just don’t want SQL Prompt to touch a certain section of code because your formatting template doesn’t quite do what you want. For example, let’ say you’ve just spent time getting a non-standard format for that section of code exactly right, but now want to apply Format SQL again, for the rest of the query.

An alternative solution for Listing 5, if you know your standard style would format the list items exactly as you want, would simply be to disable formatting for the rest of the statement, and Prompt allows you to do this easily. Simply highlight the code segment preceding the list (in Listing 5) and apply the Disable Formatting for selected text action. Comments will appear around the section of code telling SQL Prompt to disable formatting at the start of the highlighted section and reenable it at the end. Then, simply highlight the whole statement and apply your chosen style.

Listing 7

Note that you can also type the formatting control comments manually, and some spacing, casing, and trailing comments won’t stop them from working. However, the method relies on the parser seeing a double-hyphen comment (--) followed by SQL Prompt formatting Off (or On), so if for any reason you can’t or don’t use double-hyphen comments, then you will not be able to use this control method.

In Listing 8, I’ve decided that the SET clause should appear all on one line, as should the last two Booleans in the WHERE clause, so I’ve selectively applied the unformat action to each of these sections.

Listing 8

Then, since I’ve have gone to the trouble to use a custom format, I want it to stay that way, so I apply the Disable formatting for selected text to just those parts of the statement.

Listing 9

Now, if I apply Format SQL, it won’t affect those commented sections of the code. However, one issue is that the number of comments in that query is now kind of out of control, making the statement harder to read. Luckily, the formatting control comment does not have to be on its own line. However, it does have to be the first comment on the line. In Listing 10, the first Off control comment still works, but the second on doesn’t.

Listing 10

Summary

Most of the time, the goal is going to be to use the same, standard formatting for all your code. Occasionally, that formatting template won’t entirely fit your needs. For this, SQL Prompt has actions that you can apply independently of the Format SQL command, either to unformat the selected code, or to ignore the Format SQL command for that code.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more