9 January 2019
9 January 2019

How to Apply Non-Standard SQL Formatting Using SQL Prompt

SQL Prompt not only will format your code exactly as you want, but will also help you switch quickly to an alternative style, or to apply exceptions to certain parts of a SQL script, where your preferred style isn't what's required.

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

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.

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    The SQL Prompt Functionality Finder

    You have SQL Prompt, but are you aware of all the things it can do and how to get it to do it? Phil Factor provides a handy table to make it obvious.

  • Article

    Quick SQL Prompt tip - picking the columns you need for speedy results

    One of the poor practices that I see so many people doing in code is using SELECT *. This is poor practice because it takes more time to complete for clients, it’s an unnecessary use of resources on the client, server and network, and it can fill the buffer cache with rarely-used data. For example,

  • Article

    SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)

    If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere.

  • Article

    SQL Prompt Tip: how to control when the suggestion box pops up

    By default, SQL Prompt shows code auto-completion suggestions automatically, and continuously. Phil Factor shows how to control this behavior, for the times when you need it to be a little less intrusive, such as when working through more intricate coding problems that require careful thought.

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly