What are the challenges you face when working across database platforms? Take the survey
Options

[Format SQL] Odd Behaviour with Table Alias Comment

EdmundEdmund Posts: 11
edited June 8, 2011 10:05AM in SQL Prompt Previous Versions
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>

Comments

  • Options
    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
  • Options
    SSMS: 10.50.1600.1
    SQL Prompt Pro: 5.1.4.11
  • Options
    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.
  • Options
    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
  • Options
    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.
  • Options
    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
  • Options
    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.
Sign In or Register to comment.