1. SSMSBoost Add-in discussion board
  2. SSMSBoost Add-in discussion categories
  3. Discussions & Announcements
  4. Feedback on new Formatter (published in 2.13)
Andrei
  • Posts: 398
  • Joined: 7/1/2012
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
  • Posts: 1
  • Joined: 9/24/2014
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: 
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:
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:
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:
SELECT	alias1			= col1
,		a2				= col2
,		verylongalias3	= col3
FROM DATA



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





Thanks!
Morpheus
  • Posts: 2
  • Joined: 8/4/2014
Originally Posted by: Andrei

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:

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:


SELECT
	*
FROM
	tbl_BusinessPartner
WHERE
	(
		Customer = 1
		OR Supplier = 1
	)
	AND Address2 IS NOT NULL
BigPhil
  • Posts: 11
  • Joined: 10/9/2012
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'.
Борис Бондарев
  • Posts: 2
  • Joined: 4/1/2015
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
where a.a > b.a or (a.bbbbb <= b.c and a.c != b.bbbbb)

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


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


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


4. Subquery (space before/after opening/closing brackets)
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
  • Posts: 1
  • Joined: 2/5/2016
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.
  • Posts: 1
  • Joined: 9/13/2016
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
  • Posts: 287
  • Joined: 6/30/2012
Originally Posted by: Dani S.

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.
 1 user thanked SSMSBoost for this useful post.
JimmyR on 8/21/2019
David Rocke
  • Posts: 3
  • Joined: 1/14/2016
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.

SELECT * FROM ATable

Could become

SELECT 
    *
FROM
    ATable
m60freeman
  • Posts: 8
  • Joined: 6/6/2014
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
  • Posts: 22
  • Joined: 4/6/2017
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:
    
   ,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 
    
   ,CASE 
      WHEN expression=result
         THEN output 
      WHEN expression=otherresult
         THEN otherOutput
         ELSE elseOutput
   END                   AS 'columnAliasY'

  • "Insert" - "Values" lacks of "SpaceBeforeOpeningBrace" to reach
    
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:
    
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
    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 
    
--#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
  • Posts: 1
  • Joined: 1/10/2018
Originally Posted by: Duff


4. Aligning the 'END' with the 'CASE' in a case statement, for example:

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

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
  • Posts: 5
  • Joined: 2/19/2018
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
 Atom Feed Rss Feed
Similar Topics
  1. SSMSBoost Add-in discussion board
  2. SSMSBoost Add-in discussion categories
  3. Discussions & Announcements
  4. Feedback on new Formatter (published in 2.13)
  • 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.