[Format SQL] Odd Behaviour with Table Alias Comment

Edmund

Hello Red Gate,

I've recently installed SQL Prompt Pro and have noticed that Format SQL behaves oddly when table alias comments are present. For example,

SELECT P.NAME AS Parent, C.NAME AS Child
FROM  dbo.ListOfThings P --Parent
JOIN  dbo.ListOfThings C --Child
ON P.NAME = C.Parent

formats to,

SELECT   P.NAME AS Parent, C.NAME AS Child
FROM     dbo.ListOfThings P --Parent
JOIN     dbo.ListOfThings C 
--Child
         ON
         P.NAME = C.Parent

instead of,

SELECT   P.NAME AS Parent, C.NAME AS Child
FROM     dbo.ListOfThings P --Parent
JOIN     dbo.ListOfThings C --Child
ON       P.NAME = C.Parent

The spacing is better seen once pasted in SSMS (May you move to a fixed width font for the Code format on the forum). As you can see the ON condition gets indented and the comment placed on a new line for the second join. Instead the ON should stay inline with the JOIN (as it does without the comment) and the comment should stay on the same line as the table.

Can this be fixed?

Thanks,

Edmund

Below is the format style being used.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!---->
<LayoutOptions version="1" type="LayoutOptions">
  <ReservedWordsCasing>1</ReservedWordsCasing>
  <BuiltInFunctionCasing>1</BuiltInFunctionCasing>
  <BuiltInDataTypeCasing>1</BuiltInDataTypeCasing>
  <DelimitIdentifiers>False</DelimitIdentifiers>
  <IndentationAmount>3</IndentationAmount>
  <UseTabs>False</UseTabs>
  <ReformatExpressions>True</ReformatExpressions>
  <FirstColumnOnNewLine>False</FirstColumnOnNewLine>
  <CommasAtStart>False</CommasAtStart>
  <MultilineSpacesAfterCommas>False</MultilineSpacesAfterCommas>
  <MultilineSpacesBeforeCommas>True</MultilineSpacesBeforeCommas>
  <MultilineAlignCommasWithStatement>True</MultilineAlignCommasWithStatement>
  <OperatorsOnNewline>True</OperatorsOnNewline>
  <OperatorsAtStart>True</OperatorsAtStart>
  <IndentBeginToken>True</IndentBeginToken>
  <IndentWithExpressionBrackets>True</IndentWithExpressionBrackets>
  <IndentWithParameterBrackets>True</IndentWithParameterBrackets>
  <IndentWithQueryBrackets>True</IndentWithQueryBrackets>
  <CloseBracketsOnNewLine>True</CloseBracketsOnNewLine>
  <OpenBracketsOnNewLine>True</OpenBracketsOnNewLine>
  <Wrap>True</Wrap>
  <WrapWidth>120</WrapWidth>
  <ShortLength>15</ShortLength>
  <CreateBracketsAtStartOfLines>False</CreateBracketsAtStartOfLines>
  <ParameterBracketOnNewline>True</ParameterBracketOnNewline>
  <CloseParameterBracketsOnNewLine>True</CloseParameterBracketsOnNewLine>
  <CompactShortStatements>False</CompactShortStatements>
  <SinglelineSpacesAfterCommas>True</SinglelineSpacesAfterCommas>
  <SinglelineSpacesBeforeCommas>False</SinglelineSpacesBeforeCommas>
  <SpacesAroundOperators>True</SpacesAroundOperators>
  <SpacesAroundComparisons>True</SpacesAroundComparisons>
  <FirstParameterDefinitionOnNewLine>True</FirstParameterDefinitionOnNewLine>
  <SpacesAfterBrackets>False</SpacesAfterBrackets>
  <LayoutSelectStatements>True</LayoutSelectStatements>
  <LayoutCreateStatements>True</LayoutCreateStatements>
  <QueryNewlineAfterOpenBracket>True</QueryNewlineAfterOpenBracket>
  <QueryNewlineBeforeCloseBracket>True</QueryNewlineBeforeCloseBracket>
  <JoinConditionsOnNewline>True</JoinConditionsOnNewline>
  <AlignConditionsWithJoins>True</AlignConditionsWithJoins>
  <AlignJoinsWithFrom>True</AlignJoinsWithFrom>
  <IndentByKeywordWidth>False</IndentByKeywordWidth>
  <ColumnsOnSingleLine>True</ColumnsOnSingleLine>
  <DoNotIndentProcedureContents>True</DoNotIndentProcedureContents>
  <ExecuteParametersOnSingleLine>True</ExecuteParametersOnSingleLine>
  <UseSpacesInsteadOfTabs>False</UseSpacesInsteadOfTabs>
  <IndentSelectSubclauses>False</IndentSelectSubclauses>
</LayoutOptions>

chriskelly

Which version of SSMS are you using?
What version of SQL Prompt are you using? This is in the format of X.x.x.xx and be found under SQL Prompt 5 > Help > About

Edmund

SSMS: 10.50.1600.1
SQL Prompt Pro: 5.1.4.11

chriskelly

Please can you try downloading this LayoutOptions.xml file, and then through SQL Prompt Options menu (navigate to Format >Styles) select this config file in the Style dropdown menu (need to select Import...).

ftp://support.red-gate.com/patches/SQL_ ... ptions.xml

Now try the 'Format SQL' again.

Please let me know how you get on.

Edmund

Hello chrisk5,

I get the same result. The following

SELECT P.NAME AS Parent, C.NAME AS Child 
FROM  dbo.ListOfThings P --Parent 
JOIN  dbo.ListOfThings C --Child 
ON P.NAME = C.Parent

still is formated to this

SELECT   P.NAME AS Parent, C.NAME AS Child
FROM     dbo.ListOfThings P --Parent 
JOIN     dbo.ListOfThings C
--Child 
         ON
         P.NAME = C.Parent

with the imported style you provided.

Edmund

chriskelly

I think that you may have found a known issue, although it is a little odd that my config file didn't help.

Anyway could you try this. Go to the SQL Prompt options and go to Data statements (which is under Format) and ensure that 'Join condition' is not selected. Do you now see the proper results.

Edmund

Hi chrisk5,

Things format a little better with the recomendation you provided. The following

SELECT P.NAME AS Parent, C.NAME AS Child 
FROM  dbo.ListOfThings P --Parent 
JOIN  dbo.ListOfThings C --Child 
ON P.NAME = C.Parent

now formats to

SELECT   P.NAME AS Parent, C.NAME AS Child
FROM     dbo.ListOfThings P --Parent 
JOIN     dbo.ListOfThings C --Child 
         ON P.NAME = C.Parent

.
The commet stays on the same line but ON condition is indented. However, I can't use that option as I need the join condition on a new line in locations where there is no table alias comment and the join condition is currently on the same line.

I guess the good news is that this is a known issue. As I understand this thread there is no fix for this currently but it will be addressed in the next update of SQL Prompt. Correct?

Thanks,

Edmund

chriskelly

Yes you are correct, this seems to be a known issue. I have updated the bug report SP-3571 with your scenario so that the developers can take a look. At the moment though i do not have any information on when this will be tackled.