Shulou(Shulou.com)06/01 Report--

Not only can SQL Prompt format your code exactly in the format you want, it can also help you quickly switch to other styles, or apply exceptions to parts of SQL scripts that don't require a particular style.

SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. It can be installed and used, and the coding efficiency can be greatly improved. For more information, please visit Huidu. Com.

I use SQL Prompt to format my code; in most cases, I have defined standard styles so that I can lay out the code the way I like. I also have several alternative styles for specific tasks, such as comparing two versions of the same code.

However, sometimes the standard format does not work on some code or parts of that code. So, like any good tool, SQL Prompt will not only format your code, but also "unformat" you, or allow you to selectively disable certain parts of the formatting.

Unformat the code

Sometimes you just want the code or parts of the code to be in a very compact format, usually just 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

Listing 1

Obviously, I don't want to use code like * in production applications, so I apply the SQL Prompt custom style using the Format SQL command (or the Ctrl K ~ Ctrl Y shortcut). 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

Listing 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 from the actions menu, start typing unformat, and then select it from the filtered list.

When I first saw this command, I imagined that after formatting the code, it would be like an "undo" button, but this is not the case. It does not undo any refactoring that you just applied, but it removes all space characters and replaces them with a single space character. The result is that it removes all the beautiful formatting from the code and places each bit of the statement on one line (although it may appear as an automatic line wrap, depending on where it appears or your SSMS setting).

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

Listing 3

It retains the space around the comment to ensure that the double hyphen (- -) comment does not end up commenting out all the remaining code. You can occasionally use this technique to prevent prompts from breaking lines (for example, when you need to publish code online).

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

Suppose your users request a query that will only show them the details of those customers who have ordered items worth more than $18000. Listing 4 provides a 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

Listing 4

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

SELECT * FROM Sales.Customers WHERE CustomerId IN (187509825935952, … 839, 1000, 1005, 1040)

Listing 5

To send the query to the user, you may not want to apply the standard format, which may extend wildcards, make the query longer, indent the list based on the format style, and so on. In this case, you can just highlight the ID list, apply the unformat action, add some carriage returns / line feeds, 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,1177,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, 114431, 490, 807, 832, 1000, 1005, 1040)

Listing 6

Selectively disable format

Just like SQL Prompt is good at formatting code, sometimes you just don't want SQL Prompt to touch specific parts of the code, because formatting templates don't fully meet your requirements. For example, suppose you just took some time to get the non-standard format for this part of the code exactly correctly, but now you want to apply Format SQL to the rest of the query again.

Another solution in listing 5 is that if you know that the standard style will format the list items exactly as you want, you will only disable formatting for the rest of the statement, and Prompt allows you to do this easily. Just highlight the code snippet in front of the list (in listing 5) and apply Disable Formatting to the selected text operation. Comments appear in the code section, telling 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 of your choice.

-- 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,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 7

Note that you can also type format control comments manually, and some spacing, case, and ending comments do not prevent them from working. However, this method relies on the parser formatting Off (or On) with SQL Prompt after seeing double hyphen comments, 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 be displayed on one line, and the last two Boolean values in the WHERE clause should be on the same line, so I applied the unformat operation selectively to each section.

UPDATE Sales.CustomerCategories SET CustomerCategories.CustomerCategoryID = 1, CustomerCategories.CustomerCategoryName = Ninety Newcastle, CustomerCategories.LastEditedBy = 0 WHERE CustomerCategories.CustomerCategoryID = 1 AND CustomerCategories.CustomerCategoryName = Ninety New' AND CustomerCategories.LastEditedBy = 0

Listing 8

Then, because of the hassle of using a custom format, I want it to remain in this state, so I use the disable format for the selected text that applies only to those parts of the statement.

UPDATE Sales.CustomerCategories-- A code comment-- SQL Prompt formatting off SET CustomerCategories.CustomerCategoryID = 1, CustomerCategories.CustomerCategoryName = NissNewswire, CustomerCategories.LastEditedBy = 0-- SQL Prompt formatting on WHERE CustomerCategories.CustomerCategoryID = 1-- Another comment-- SQL Prompt formatting off AND CustomerCategories.CustomerCategoryName = Noble New' AND CustomerCategories.LastEditedBy = 0;-- SQL Prompt formatting on

Listing 9

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

UPDATE Sales.CustomerCategories-- SQL Prompt formatting off-- A code comment SET CustomerCategories.CustomerCategoryID = 1, CustomerCategories.CustomerCategoryName = NissNewswire, CustomerCategories.LastEditedBy = 0-- SQL Prompt formatting on WHERE CustomerCategories.CustomerCategoryID = 1-- Another comment-- SQL Prompt formatting off AND CustomerCategories.CustomerCategoryName = Noble New' AND CustomerCategories.LastEditedBy = 0;-- SQL Prompt formatting on

Listing 10

Summary

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.