logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Andrei  
#1 Posted : Monday, August 18, 2014 4:12:45 AM(UTC)
Rank: Administration
Well, despite the newly published formatter is kind of preview release, some users are already using it and report us further proposals.
We ask and encourage everyone, who likes and needs formatter to write here proposals of even publish here formatting templates (they are saved like XML files on disk).
Your feedback will be considered and appreciated !
Duff  
#2 Posted : Wednesday, September 24, 2014 9:34:23 PM(UTC)
Rank: Newbie
Hi,

I've just downloaded 2.14 to give the new formatter a try. It's a good tool, but if it could deal with these scenarios too then it would be fantastic:

1. Aligning parentheses. Ideally this would be configurable based upon the number of characters between the matching parentheses.
For example:
Code:
SELECT D.col3
FROM DATA AS D
WHERE EXISTS
( -- I think the best general rule is if the gap between the parentheses is big, then have them start and end on a new line like this
	SELECT 1
	FROM OTHER AS O
	WHERE O.col1 = D.col1
	AND O.col2 = D.col2
)


But while at the same time, still allowing parentheses to remain in a single line when they're close together:
Code:
DECLARE @this NUMERIC(16,6) -- parentheses in data types (close together)
,		@that VARCHAR(50)

SELECT col3 = ISNULL(D.col3,0) -- function parentheses. Normally close together.  It's fine to wrap into multiple lines if the gap is very big.
FROM DATA AS D
WHERE D.col4 = 1
AND (@this = 3 OR @that = 'Value') -- parentheses in simple logic (still quite close together)



2. Aligning multi-part / multi-line join conditions, for example:
Code:
SELECT	D.col7
,		O.col8
FROM 		DATA 	AS D
INNER JOIN 	OTHER 	AS O
	ON O.col1 = D.col2
	AND O.col3 = D.col4
	AND O.col5 = D.col6



3. Aligning column aliases (similar to how you already do this with UPDATE statements), for example:
Code:
SELECT	alias1			= col1
,		a2				= col2
,		verylongalias3	= col3
FROM DATA



4. Aligning the 'END' with the 'CASE' in a case statement, for example:
Code:
SELECT	
	calcval =	CASE
					WHEN col1 = 1 THEN 100
					WHEN col2 = 1 THEN 200
					ELSE 300
				END
FROM DATA





Thanks!
Morpheus  
#3 Posted : Monday, March 2, 2015 9:56:41 AM(UTC)
Rank: Newbie
Originally Posted by: Andrei Go to Quoted Post
Well, despite the newly published formatter is kind of preview release, some users are already using it and report us further proposals.
We ask and encourage everyone, who likes and needs formatter to write here proposals of even publish here formatting templates (they are saved like XML files on disk).
Your feedback will be considered and appreciated !



Dear Andrei,

I was using the "old style formatter" for years, and like the new formatter as I now can customize it much better. Thanks for the WYSIWYG editor it helps alot to do the customizing.

Something I miss is the formatting of the WHERE conditions:

Sample:

Code:
SELECT * FROM tbl_BusinessPartner WHERE (Customer = 1 OR Supplier = 1) AND Address2 IS NOT NULL


The only option I have is to insert a line break after the keyword WHERE but nothing regarding the conditions, I'd like to hve it like this:

Code:

SELECT
	*
FROM
	tbl_BusinessPartner
WHERE
	(
		Customer = 1
		OR Supplier = 1
	)
	AND Address2 IS NOT NULL
BigPhil  
#4 Posted : Wednesday, December 30, 2015 2:02:45 PM(UTC)
Rank: Member
I would imagine that this question has been asked before, though I could not find it.

It would be great if there were an additional 'Terminator' node in the formatting options. Setting this to 'Semicolon' (from options 'Semicolon' and (default) 'Unspecified') would mean that a semicolon terminator would be added to all statements requiring them, on choosing 'Format SQL'.
Борис Бондарев  
#5 Posted : Monday, January 11, 2016 6:54:57 PM(UTC)
Rank: Newbie
New foramtter is a very helpfull tool, thank you!
I'd like to see some new features:

1. Space before/after condition/and/or, e.g. newline
this one
Code:
where a.a > b.a or (a.bbbbb <= b.c and a.c != b.bbbbb)

should be transformed into
Code:
where
a.a > b.a
or
(
a.bbbbb <= b.c
and
a.c != b.bbbbb
)


2. Identing conditions/subconditions, e.g. tab
Code:
where
       a.a > b.a
       or
       (
             a.bbbbb <= b.c
             and
             a.c != b.bbbbb
       )


3. Alligning operators
Code:
where
       a.a > b.a
       or
       (
             a.bbbbb <= b.c
             and
             a.c     != b.bbbbb
       )


4. Subquery (space before/after opening/closing brackets)
Code:
select
       aaa =
       (
             select
                    null
       )


5. Add some exclusion possibility, for example - do not affect lines, containing both line comments (--) and instruction.

6. Add space variant «double newline» and/or add possibility declare user defined separators.
Palle  
#6 Posted : Friday, February 5, 2016 7:02:00 AM(UTC)
Rank: Newbie
i tried to create a format template that only changed keywords to uppercase, i wanted to keep the actual formatting.

set all options to false or unspecified, and "KeywordsCase" to "UpperCase".

but this deletes all of my indentations.

it would be nice to also have an "unspecified" value for the indentations.

/palle

ver 2.17
Dani S.  
#7 Posted : Tuesday, September 13, 2016 11:43:36 AM(UTC)
Rank: Newbie
Hi, the SQL Formatter is excellent and I can make the code follow my formatting taste.

However, there is a very simple thing I miss: Indentation is done always using a TAB character. My SSMS query editor is configured to replace TABs with spaces, but the TABs introduced by the formatted are not changed (because they do not come from user input, I presume). Moreover, my indentation preference is 2 spaces.

It would be great if the indentation style could be could be customised. This feature was present in the Old fashioned formatting as 'Indent String', but it looks like this feature has not been ported to the new formatter (or at least I couldn't find it!)

Thanks

SSMSBoost  
#8 Posted : Tuesday, September 13, 2016 12:25:00 PM(UTC)
Rank: Administration
Originally Posted by: Dani S. Go to Quoted Post
Hi, the SQL Formatter is excellent and I can make the code follow my formatting taste.

However, there is a very simple thing I miss: Indentation is done always using a TAB character. My SSMS query editor is configured to replace TABs with spaces, but the TABs introduced by the formatted are not changed (because they do not come from user input, I presume). Moreover, my indentation preference is 2 spaces.

It would be great if the indentation style could be could be customised. This feature was present in the Old fashioned formatting as 'Indent String', but it looks like this feature has not been ported to the new formatter (or at least I couldn't find it!)

Thanks



Thank you Dani,
we will check, if we can fix it quickly. If not - then will be done in +1 Version. We have scheduled work on Formatter, where we will consider many proposals made here on Forum.
David Rocke  
#9 Posted : Thursday, January 26, 2017 10:59:18 AM(UTC)
Rank: Newbie
What I have suggested is a way of defining Composite whitespace character sequences using the built in whitespace characters e.g. Tab/Space/Newline so that you could have "NewlineTab" = Newline + Tab which could then be used from the drop down lists in the format options.

Code:
SELECT * FROM ATable

Could become
Code:

SELECT 
    *
FROM
    ATable
m60freeman  
#10 Posted : Monday, May 15, 2017 7:10:36 PM(UTC)
Rank: Newbie
In JOINs, it can't keep ON and ANDs for a join either on one line or aligned. It wants to put the ON on the same line as the JOIN but the AND on the following line.

I'd prefer to either keep the AND clauses for a JOIN on the same line or put the ANDs on new lines but aligned with the ON.

Ideally, align the ON clauses for all the joins in a statement regardless of how the ANDs are handled.
JimmyR  
#11 Posted : Friday, November 17, 2017 9:45:16 AM(UTC)
Rank: Member
Hi SSMSBOOST- Team

The new formatter is a VERY GREAT approach, I'm getting a real fan!

A couple of things I found, using SSMSBOOST 3.1.6458.15317 (absolutely incomplete list, more to come):
  • The issue with the Tab using to do the indentation mentioned by Dani S. is still there.
  • "Case" formatting lacks:
    1. SpaceBeforeWhen
    2. SpaceBeforeElse

    I want to reach for bistable when/then/else statements:
    Code:
    
       ,CASE WHEN (ISNUMERIC(a.Number) = 0 AND a.[Group]='XYZ')
          THEN CONCAT(xyz_a.[Type], ' ' , bra_a.[Name])
          ELSE CONCAT(a.[Type], ' ' , a.[Name])
       END                   AS 'columnAliasX'
       ,CASE WHEN (ISNUMERIC(a.Number) = 0 AND a.[Group]='XY')
          THEN xyz_a.[Group] 
          ELSE a.[Group]
       END                   AS 'columnAliasY'
    

    or this look for multiple when/then
    Code:
    
       ,CASE 
          WHEN expression=result
             THEN output 
          WHEN expression=otherresult
             THEN otherOutput
             ELSE elseOutput
       END                   AS 'columnAliasY'
    

  • "Insert" - "Values" lacks of "SpaceBeforeOpeningBrace" to reach
    Code:
    
    INSERT INTO tablename
    	(column1, column2, column3)
    	VALUES 
       ('value1','value2','value3')
    

  • Option to keep a marked section untouched (for example a variable declaration section which fills a table variable with data.
    Example:
    Code:
    
    DECLARE @test TABLE (id int identity(1,1), keyCol varchar(50), valCol varchar(256))
    INSERT @test (keyCol, valCol)
       SELECT 'param1','Value1' UNION
       SELECT 'param2','Value2' UNION
       SELECT 'param3','Value3' 

    gets this way, after formatting, which makes no sense (IMHO) and needs unnecessary many lines
    Code:
    DECLARE @test TABLE ( id     int IDENTITY(1,1)
    		,               keyCol varchar(50)
    		,               valCol varchar(256)
    	)
    INSERT @test
    	(keyCol, valCol)
    	SELECT
    		 'param1'
    		,'Value1'
    	UNION
    	SELECT
    		 'param2'
    		,'Value2'
    	UNION
    	SELECT
    		 'param3'
    		,'Value3'

    Maybe somethin like
    Code:
    
    --#beginNoAutoFormat
    DECLARE @test TABLE (id int identity(1,1), keyCol varchar(50), valCol varchar(256))
    INSERT @test (keyCol, valCol)
       SELECT 'param1','Value1' UNION
       SELECT 'param2','Value2' UNION
       SELECT 'param3','Value3' 
    --#endNoAutoFormat
    


As mentioned above: more ideas to come, if desired.

Regards
Jimmy
Miles Oden  
#12 Posted : Wednesday, January 10, 2018 8:35:38 PM(UTC)
Rank: Newbie
Originally Posted by: Duff Go to Quoted Post

4. Aligning the 'END' with the 'CASE' in a case statement, for example:
Code:
SELECT	
	calcval =	CASE
					WHEN col1 = 1 THEN 100
					WHEN col2 = 1 THEN 200
					ELSE 300
				END
FROM DATA



I'd like to second Duff's request for aligning CASE and END (i.e. an "AlignEnd" option) and also request the option of "Indent" and "SpaceBefore" for the entire Case Statement (i.e. not inside the Branches section). Ideally there would also be a "SpaceBeforeColumn" option that would be used when the Case statement is used as the value for a column in the SELECT list (lines 4-8 below) as opposed to when the Case statement is used in a comparison in FROM or WHERE (lines 14-18 below):
Code:

SELECT	
	col1
	, col2     
	, CASE  --Controlled by "SpaceBeforeInSelect"
		  WHEN col1 = 1 THEN 100
		  WHEN col2 = 1 THEN 200
		  ELSE 300
	  END as newcol1  --Controlled by "AlignEnd"
	, col3
FROM DATA
WHERE
	col1>0
	AND col3 <=
		CASE  --Controlled by "SpaceBefore" and "Indent"
			WHEN col1 = 1 THEN 100
			WHEN col2 = 1 THEN 200
			ELSE 300
		END --Controlled by "AlignEnd"
	AND col2 >0


joid  
#13 Posted : Sunday, March 17, 2019 9:02:10 AM(UTC)
Rank: Newbie
Hi,
One more thing not mentioned before: try/catch block is not formatted as standard logical block. Would be nice to have same formatting as standard begin/end, or dedicated try/catch formatting:
Example
Rss Feed  Atom Feed
Users browsing this topic
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.