How to Apply Non-Standard SQL Formatting Using SQL Prompt
How to Apply Non-Standard SQL Formatting Using SQL Prompt
Learn how to apply non-standard SQL formatting using SQL Prompt.
Join the DZone community and get the full member experience.Join For Free
Read the 2019 State of Database DevOps Report for the very latest insights
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, in 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.
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
SELECT * FROM Sales.Customers
Clearly, I don’t want code like this, using *, in my production application, so I apply my SQL Prompt custom style using the FormatSQL command (or the CtrlK,CtrlY shortcut). I’ve also configured the Prompt Format>Styles options so that certain formatting actions run as part of this command, including ExpandWildcards and InsertSemicolons. I end up with a statement that will be 32 lines long, as you see in the Listing 2.
SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID, BuyingGroupID, PrimaryContactPersonID, AlternateContactPersonID, DeliveryMethodID, DeliveryCityID, PostalCityID, CreditLimit, AccountOpenedDate, StandardDiscountPercentage, IsStatementSent, IsOnCreditHold, PaymentDays, PhoneNumber, FaxNumber, DeliveryRun, RunPosition, WebsiteURL, DeliveryAddressLine1, DeliveryAddressLine2, DeliveryPostalCode, DeliveryLocation, PostalAddressLine1, PostalAddressLine2, PostalPostalCode, LastEditedBy, ValidFrom, ValidTo FROM Sales.Customers;
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).
SELECT Customers.CustomerID, Customers.CustomerName, Customers.BillToCustomerID, Customers.CustomerCategoryID, Customers.BuyingGroupID, Customers.PrimaryContactPersonID, Customers.AlternateContactPersonID, Customers.DeliveryMethodID, Customers.DeliveryCityID, Customers.PostalCityID, Customers.CreditLimit, Customers.AccountOpenedDate, Customers.StandardDiscountPercentage, Customers.IsStatementSent, Customers.IsOnCreditHold, Customers.PaymentDays, Customers.PhoneNumber, Customers.FaxNumber, Customers.DeliveryRun, Customers.RunPosition, Customers.WebsiteURL, Customers.DeliveryAddressLine1, Customers.DeliveryAddressLine2, Customers.DeliveryPostalCode, Customers.DeliveryLocation, Customers.PostalAddressLine1, Customers.PostalAddressLine2, Customers.PostalPostalCode, Customers.LastEditedBy, Customers.ValidFrom, Customers.ValidTo FROM Sales.Customers;
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.
SELECT DISTINCT CustomerId--,Quantity * OrderLines.UnitPrice FROM Sales.Orders JOIN Sales.OrderLines ON OrderLines.OrderID = Orders.OrderID WHERE Quantity * OrderLines.UnitPrice > 18000;
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.
SELECT * FROM Sales.Customers WHERE CustomerId IN ( 187, 509, 825, 935, 952, … 839, 1000, 1005, 1040 )
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.
SELECT * FROM Sales.Customers WHERE CustomerId IN ( 187, 509, 825, 935, 952, 1003, 415, 529, 583, 1006, 1018, 1056, 96, 166, 201, 472, 489, 863, 999, 15, 32, 45, 401, 899, 948, 988, 101, 527, 961, 26, 105, 427, 521, 813, 71, 413, 420, 480, 821, 37, 943, 950, 1010, 50, 102, 170, 460, 492, 560, 578, 815, 16, 117, 405, 410, 533, 922, 949, 954, 498, 829, 1017, 42, 67, 418, 834, 854, 861, 874, 911, 858, 892, 894, 437, 907, 912, 962, 14, 107, 524, 550, 930, 973, 40, 114, 431, 490, 807, 832, 839, 1000, 1005, 1040 )
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.
-- SQL Prompt formatting off SELECT * FROM Sales.Customers WHERE CustomerId IN -- SQL Prompt formatting on ( 187, 509, 825, 935, 952, 1003, 415, 529, 583, 1006, 1018, 1056, 96, 166, 201, 472, 489, 863, 999, 15, 32, 45, 401, 899, 948, 988, 101, 527, 961, 26, 105, 427, 521, 813, 71, 413, 420, 480, 821, 37, 943, 950, 1010, 50, 102, 170, 460, 492, 560, 578, 815, 16, 117, 405, 410, 533, 922, 949, 954, 498, 829, 1017, 42, 67, 418, 834, 854, 861, 874, 911, 858, 892, 894, 437, 907, 912, 962, 14, 107, 524, 550, 930, 973, 40, 114, 431, 490, 807, 832, 839, 1000, 1005, 1040 );
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
SQLPromptformatting 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.
UPDATE Sales.CustomerCategories SET CustomerCategories.CustomerCategoryID = 1, CustomerCategories.CustomerCategoryName = N'New', CustomerCategories.LastEditedBy = 0 WHERE CustomerCategories.CustomerCategoryID = 1 AND CustomerCategories.CustomerCategoryName = N'New' AND CustomerCategories.LastEditedBy = 0;
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.
UPDATE Sales.CustomerCategories -- A code comment -- SQL Prompt formatting off SET CustomerCategories.CustomerCategoryID = 1, CustomerCategories.CustomerCategoryName = N'New', CustomerCategories.LastEditedBy = 0 -- SQL Prompt formatting on WHERE CustomerCategories.CustomerCategoryID = 1 -- Another comment -- SQL Prompt formatting off AND CustomerCategories.CustomerCategoryName = N'New' AND CustomerCategories.LastEditedBy = 0; -- SQL Prompt formatting on
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.
UPDATE Sales.CustomerCategories -- SQL Prompt formatting off –- A code comment SET CustomerCategories.CustomerCategoryID = 1, CustomerCategories.CustomerCategoryName = N'New', CustomerCategories.LastEditedBy = 0 -- SQL Prompt formatting on WHERE CustomerCategories.CustomerCategoryID = 1 -- Another comment -- SQL Prompt formatting off AND CustomerCategories.CustomerCategoryName = N'New' AND CustomerCategories.LastEditedBy = 0; -- SQL Prompt formatting on
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.
Published at DZone with permission of Louis Davidson , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.