SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Code Formatting inserting new line

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Code Formatting inserting new line
Author Message
jeff.williams
Guest





Post Code Formatting inserting new line Reply with quote
Version: 3.5.30 - SQL Server 2008 SSMS

When I setup a custom select format for T-SQL, and put a space in front of SELECT - the code formatter inserts a new line prior to the select statement. If I highlight just that select statement - the full statement is moved to the right by one space.

Example:

If I setup the code formatter as:

SELECT ... AS ...
,... = ...
,(...)
INTO ...
FROM ... AS ...
,(...)
JOIN ... ON ... = ...
AND ...
,(...)
WHERE ... = ...
AND ...
OR (...)
GROUP BY
...
,(...)
HAVING ...
,(...)
ORDER BY
...
,(...)

Then, hit F11 with the query SELECT * FROM mytable; as the only thing in the query window - it will be formatted correctly. If I hit Ctrl-F11 again, a new line is entered prior to the select statement (actually, it appears to be a space and a new line are entered). If I now highlight just that select statement, instead of inserting a new line - the whole statement is moved right by one space.

Select *
From dbo.MyTable mt;

Becomes:

Select *
From dbo.MyTable mt;

Is this working as designed - or, is there some way to suppress this behavior?
Mon Aug 25, 2008 2:17 pm
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Would you please repost your message with code sensitive parts wrapped into code blocks, so we can see the exact text?

Basically when posting, in the text of the message select a text block for the SQL code or pattern, in which you want to preserve the spaces and tabs then click [Code] link above the message text area.
Mon Aug 25, 2008 2:46 pm View user's profile Send private message
jeff.williams



Joined: 28 Aug 2008
Posts: 3
Country: United States

Post Reply with quote
Code:

 SELECT ... AS ...
   ,... = ...
   ,(...)
   FROM ... AS ...
   ,...
  JOIN ... ON ... = ...
    AND ...
   ,(...)
  WHERE ... = ...
    AND ...
     OR (...)
  GROUP BY ...
           ,(...)
 HAVING ...
        ,(...)
  ORDER BY ...
           ,(...)


In the above, I have a single space before SELECT.
Thu Aug 28, 2008 11:51 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Ok I see it now. The following paragraphs describe in brief how this stuff works.

If you select some text and press Ctrol+F11 ,only that selected text is formatted, starting from the position of the first selected character.

If nothing is selected in the editor, the entire text is formatted starting from line 1, column 1.

The indenting depends on where the selection starts and position of each statements within block statements such as begin… end , begin try … while…loop and similar and controlled by the rules defined for block statements.

An empty line is inserted between statements if it is not already there. This is where the line comes when you format the text and the statement you are referring to is not the first one selected, or if nothing is selected at all. Obviously when you select just that statement, no line is inserted because it is the first one formatted..

The space before SELECT keyword controls position of SELECT keyword within SELECT statement, which also depends on which text is selected or not selected for formatting. The other keywords and text within the SELECT statement are indented relative to their position within the SELECT formatting rule. For example, if you add a space in front of FROM keyword, you will see that space added to the text of the formatted. If you add a tab character, you will see tab added.

Hope this clarifies the behavior of the formatter.

Note that there will be significant changes in version 4, the formatter will get a big facelift and provide a lot more customization options that can be applied to the entire formatting style level on top of formatted rules designed for individual statements.
Thu Aug 28, 2008 12:52 pm View user's profile Send private message
jeff.williams



Joined: 28 Aug 2008
Posts: 3
Country: United States

Post Reply with quote
Okay, I understand all of that - but I am still confused as to why this happens. Here is a better example:

Code:

SELECT * FROM dbo.mytable mt;


Hit Ctrl-F11 with this as the only thing in the query window and it is reformatted as:

Code:

 SELECT *
   FROM dbo.mytable mt;


Notice, no new line is added but I have a single space in front of SELECT. Now, if I hit Ctrl-F11 again this is what happens:

Code:


 SELECT *
   FROM dbo.mytable mt;


Here, a new line is added before the select statement. If I modify the formatting rules and remove the space in front of the SELECT this does not happen. Without the space in front of SELECT I can hit Ctrl-F11 as many times as I want and there will not be any changes.

Now, if I highlight the above text in the query window and hit Ctrl-F11 here is what I get:

Code:

  SELECT *
    FROM dbo.mytable mt;


Notice here that there are now two spaces before SELECT. It does not matter if I highlight starting at SELECT or before it - it moves the code that is highlighted to the right by one character. I can understand how this one is happening based upon the rules - but, it really shouldn't move the code at all.

So, when is version 4 going to be released?
Thu Aug 28, 2008 1:54 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
First time you had a statement starting from the beginning of the text in the editor, first character was S. Second time you had a statement whose text technically didn't start from the beginning of the text in the editor, there was a space in front of SELECT. As a result, a new line got inserted, and the rest – the SELECT statement beginning with SELECT keyword was formatted according to your rule. The rule says that every time a SELECT statement is found, it should be formatted as in the rule, meaning a space is added, then SELECT keyword, then the rest formatted as specified in the pattern. It is important to note that the SELECT starts with letter S, anything before that is not part of the statement. Spaces added previously are not considered retrospectively to be part of the statement. As you see, the mechanics are quite simple.


By the way, if you are looking for indenting control, you should be using proper structures, namely wrapping SQL blocks in BEGIN…END constructs. I know that in T-SQL they are not required for bodies of procedures and triggers, but that doesn't mean that not using them is a good practice. All other database systems as well as ANSII standard do require specific markers indicating beginning and ending of compound statements. Microsoft itself is using them in all system procedures. all commercial packages, and also having them in all code samples, virtually everywhere....

So, when proper structures are coded, the indenting is done during formatting using rules for block structures

As an example, try formatting

Code:
BEGIN
SELECT * FROM My table

    SELECT * From Mytable

SELECT *
FROM MyTable
END

Thu Aug 28, 2008 2:19 pm View user's profile Send private message
jeff.williams



Joined: 28 Aug 2008
Posts: 3
Country: United States

Post Reply with quote
Thanks for the explanation
Thu Aug 28, 2008 2:38 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.