Precise code formatting, fast style switching?All you need is SQL Prompt!

Keywords: SQL Server SQL Database encoding REST

SQL Prompt not only formats the code exactly as you want, but also helps you quickly switch to other styles or apply exceptions to parts of SQL scripts that don't require specific styles.

SQL Prompt automatically retrieves object names, syntax, and code snippets from the database to provide users with appropriate code choices.Automatic script settings make the code easy to read -- especially if the developer is not familiar with scripts.Installation is ready to use, which greatly improves encoding efficiency.For more information, please go to Huidu.com~

I use SQL Prompt to format my code; in most cases, I've defined standard styles that let me lay out the code the way I like it.For specific tasks, I have several alternative styles, such as comparing two versions of the same code.

However, sometimes the standard format does not work for some code or parts of it.Therefore, like any great tool, SQL Prompt not only formats your code for you, it also "unformats" you, or allows you to selectively disable some parts of formatting.

Unformat Code

Sometimes you just want the code or parts of the code to be in a very compact format, usually because the formatted version takes up too many lines and becomes difficult to read.For example, consider the simple query against the WideWorldImporters database in Listing 1.

SELECT *
  FROM   Sales.Customers

List 1

Obviously, I don't want to use code like * in production applications, so I use the Format SQL command (or Ctrl K, Ctrl Y shortcut) to apply the SQL Prompt custom style.I also configured the Prompt Format > Styles option so that some formatting operations run as part of this command, including Expand Wildcards and Insert Semicolons.I ended up with a 32-line statement, as shown in 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;

List 2

I want the SELECT list to be in a more concise format, so I can use an interesting action called unformat.Highlight the entire statement in Listing 2, then start typing unformat from the Action menu, and select it from the filtered list.

When I first saw this command, I imagined it would be like an Undo button after formatting the code, but that was not the case.It will not undo the impact of any refactoring you just applied, but will delete all space characters and replace them with a single space character.The result is that it removes all the nice formatting from the code and places each of the statements on a single line (although it may appear to wrap automatically, depending on where it appears or 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;

List 3

It will preserve the space around the comment to ensure that the double hyphen (--) comment does not eventually comment out all remaining code.You can use this technique occasionally to prevent prompts from making lines longer than newlines (for example, when you need to publish code online).

Of course, more common format cancellation does not require a complete statement, it is only part of it.Fortunately, if only the text of the comment is highlighted, the unformat action can work on a statement or even on any part of the comment.I've worked on a lot of times when working on a lot of projects, usually using the "Copy as IN" clause function.

Assuming your users request a query, it will only show them the details of those customers who have ordered goods worth more than $18,000.Listing 4 will provide a list of CustomerId values for those High Expense customers.

SELECT DISTINCT CustomerId--,Quantity * OrderLines.UnitPrice
  FROM   Sales.Orders
             JOIN Sales.OrderLines
                 ON OrderLines.OrderID = Orders.OrderID
  WHERE Quantity * OrderLines.UnitPrice > 18000;

Listing 4

In the grid results, highlight the column, right-click and select the Copy as IN clause, which allows you to quickly generate the query the user needs.However, it has almost 100 rows.

SELECT *
  FROM   Sales.Customers
  WHERE  CustomerId
  IN
  (
  187,
  509,
  825,
  935,
  952,
  ...
  839,
  1000,
  1005,
  1040
  )

List 5

To send a query to a user, you may not want to apply a standard format, which may expand wildcards, make the query longer, indent the list according to the formatting style, and so on.In this case, you can just highlight the ID list, apply the unformat operation, add some carriage returns/line breaks, and the query will look like 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
  )

Listing 6

Optionally Disable Formatting

Just like SQL Prompt is great at formatting code, sometimes you just don't want SQL Prompt to touch a specific part of the code because formatting templates don't fully meet your requirements.For example, suppose you just spent some time getting the nonstandard format for this part of the code exactly right, but now you want to apply Format SQL again to the remaining queries.

Another solution to Listing 5 is to disable formatting only for the rest of the statement if you know that standard styles will format the list items exactly as they need to, and Prompt allows you to do so easily.Simply highlight the code snippet in front of the list (in Listing 5) and apply Disable Formatting to the selected text operation.The comment appears in the code section and tells SQL Prompt to disable formatting at the beginning of the highlighted section and to re-enable it at the end.Then, simply highlight the entire statement and apply the style you chose.

-- 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
      );

List 7

Note that you can also type formatting control comments manually, and that some spacing, capitalization, and closing comments do not prevent them from working.However, this method relies on the parser to see double hyphen comments (--) followed by SQL Prompt to format Off (or On), so if for some reason you cannot or do not use double hyphen comments, you will not be able to use this control method.

In Listing 8, I decided that the SET clause should all appear on one line, and that the last two Booleans in the WHERE clause should also appear on the same line, so I applied the unformat operation selectively to each section.

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;

Listing 8

Then, because of the hassle of using a custom format, I want it to stay this way, so I use the Disabled format for the selected text only to 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

Listing 9

Now, if I apply Format SQL, it won't affect those comment parts of the code.However, one problem is that the number of comments in the query is now out of control, making the statement more difficult to read.Fortunately, format control comments do not have to be displayed separately.However, it must be the first comment on that line.In Listing 10, the first Off control comment is still valid, but the second is invalid.

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

List 10

summary

Most of the time, the goal is to use the same standard format for all code.Sometimes the format template does not meet your needs.To do this, SQL Prompt has actions that can be applied independently of the Format SQL command to cancel formatting the selected code or ignore the Format SQL command for that code.


Posted by Confessions on Sat, 21 Dec 2019 11:17:52 -0800