New features added to SQL Prompt Watch now.

New Error with Tabs: Block after BEGIN all the way to the left

Matthew_SontumMatthew_Sontum Posts: 26 Bronze 1
edited August 1, 2018 1:43PM in SQL Prompt
Not sure exactly which version introduced this error, but I am now seeing the following behavior:

	IF @Country = 'USA' BEGIN
SET @FormatCode = 1;
	END;
	ELSE BEGIN
SET @FormatCode = 2;
	END;<br>

In that the code inside the BEGIN/END block is flush with the left side of the screen, regardless of how far the IF statement is to the right. Here is another example:

		IF @MatchPhysicalKey > 1 BEGIN
SET @CustomerMatchKey = @MatchPhysicalKey;
RETURN @@ERROR;
		END;

It does not however, affect the statements where the formatter puts the BEGIN on the following line:

		IF @RC <> 0
		BEGIN
			SET @ReturnMessage = 'ERROR: Unable to match on physical address';
			RAISERROR(@ReturnMessage, 16, 1);
			RETURN @@ERROR;
		END;

Tagged:

Comments

  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    I'm unclear of what's happening here. I don't get the before/after view here. Is this post formatting? I don't see this behavior, but I wonder if I'm not reproducing this correctly.

  • I see it correctly on my screen. Let me try another time.

    Here is what is currently happening:
    	IF @Country = 'USA' BEGIN
    SET @FormatCode = 1;
    	END;
    	ELSE BEGIN
    SET @FormatCode = 2;
    	END;

    And here is what should be happening / what did happen in previous versions:
    	IF @Country = 'USA' BEGIN
    		SET @FormatCode = 1;
    	END;
    	ELSE BEGIN
    		SET @FormatCode = 2;
    	END;

  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    This is what I see


  • Matthew_SontumMatthew_Sontum Posts: 26 Bronze 1
    edited August 23, 2018 5:16PM
    Yes, perhaps it would be easier to see with images:

    Before:


    After:


    The problem is that the SET statements have no tabs preceding them, when they should have 2 tabs each.

    This is using my custom style sheet, although you can see a similar but different problem using the collapsed style sheet:



    In this one it is using spaces instead of tabs, but the same problem is present on the first SET statement, in that there are no spaces or tabs.

  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    Can you upload your formatting file? Is that what you mean by style sheet?
  • Yes, here is the stylesheet I am using (It won't allow me to attach it)

    <?xml version="1.0" encoding="utf-8"?>
      <AlignCaseElseToWhen>true</AlignCaseElseToWhen>
      <AlignToTab>false</AlignToTab>
      <BetweenAndAlignment>ToBetween</BetweenAndAlignment>
      <BooleanOperatorAlignment>Tabbed</BooleanOperatorAlignment>
      <BooleanOperatorBreakType>ChopIfLong</BooleanOperatorBreakType>
      <BreakAssignmentIfLong>false</BreakAssignmentIfLong>
      <BreakOnConstraints>false</BreakOnConstraints>
      <BuiltInDataTypeCasing>Uppercase</BuiltInDataTypeCasing>
      <BuiltInFunctionCasing>Uppercase</BuiltInFunctionCasing>
      <CaseEndAlignment>ToCase</CaseEndAlignment>
      <CaseExpressionAlignment>Tabbed</CaseExpressionAlignment>
      <CaseWhenElseAlignment>Tabbed</CaseWhenElseAlignment>
      <ClauseAlignment>ToStatement</ClauseAlignment>
      <ClauseIndentation>0</ClauseIndentation>
      <ClosingParenthesisAlignment>ToOpeningBracket</ClosingParenthesisAlignment>
      <CollapseCaseExpressionIfShort>true</CollapseCaseExpressionIfShort>
      <CollapseCaseIfShortCharacterCount>100</CollapseCaseIfShortCharacterCount>
      <CollapseIfShortControlFlowCharacterCount>100</CollapseIfShortControlFlowCharacterCount>
      <CollapseIfShortDdlCharacterCount>100</CollapseIfShortDdlCharacterCount>
      <CollapseIfShortDmlCharacterCount>100</CollapseIfShortDmlCharacterCount>
      <CollapseIfShortParenthesesContentsCharacterCount>100</CollapseIfShortParenthesesContentsCharacterCount>
      <CollapseIfShortSubqueryCharacterCount>100</CollapseIfShortSubqueryCharacterCount>
      <CollapseShortControlFlowStatements>true</CollapseShortControlFlowStatements>
      <CollapseShortDdlStatements>true</CollapseShortDdlStatements>
      <CollapseShortDmlStatements>true</CollapseShortDmlStatements>
      <CollapseShortParenthesesContents>true</CollapseShortParenthesesContents>
      <CollapseShortSubqueries>true</CollapseShortSubqueries>
      <CommaAlignment>ToList</CommaAlignment>
      <ConstraintColumnsBreakType>ChopIfLong</ConstraintColumnsBreakType>
      <CreateAlterClosingParenthesisAlignment>ToOpeningBracket</CreateAlterClosingParenthesisAlignment>
      <CreateAlterIndentParenthesisedExpression>true</CreateAlterIndentParenthesisedExpression>
      <CreateAlterOpeningParenthesisAlignment>ToStatement</CreateAlterOpeningParenthesisAlignment>
      <CreateAlterOpeningParenthesisBreakType>ChopAlways</CreateAlterOpeningParenthesisBreakType>
      <CreateAlterParenthesisLayout>ExpandedSplit</CreateAlterParenthesisLayout>
      <CreateAlterParenthesisedExpressionBreakType>ChopAlways</CreateAlterParenthesisedExpressionBreakType>
      <CreateAlterPlaceClosingParenthesisOnNewLine>true</CreateAlterPlaceClosingParenthesisOnNewLine>
      <CreateTableBreakBeforeFirstListItem>true</CreateTableBreakBeforeFirstListItem>
      <CteAsAlignment>ToStatement</CteAsAlignment>
      <CteColumnsOpeningParenthesisAlignment>ToStatement</CteColumnsOpeningParenthesisAlignment>
      <CteColumnsOpeningParenthesisBreakType>Never</CteColumnsOpeningParenthesisBreakType>
      <CteContentsOpeningParenthesisAlignment>ToStatement</CteContentsOpeningParenthesisAlignment>
      <CteContentsOpeningParenthesisBreakType>Never</CteContentsOpeningParenthesisBreakType>
      <CteContentsParenthesisLayout>ExpandedSplit</CteContentsParenthesisLayout>
      <CteContentsParenthesisedExpressionAlignment>ToStartOfOpeningBracket</CteContentsParenthesisedExpressionAlignment>
      <CteContentsParenthesisedExpressionBreakType>Never</CteContentsParenthesisedExpressionBreakType>
      <FirstCreateAlterDefintionBreakType>ChopIfLongOrMultipleItems</FirstCreateAlterDefintionBreakType>
      <FirstCreateProcedureParamBreakType>ChopAlways</FirstCreateProcedureParamBreakType>
      <FirstListItemBreakType>Never</FirstListItemBreakType>
      <FromClauseFirstItemBreakType>Never</FromClauseFirstItemBreakType>
      <FunctionArgumentsBreakType>SimpleWrap</FunctionArgumentsBreakType>
      <GlobalVariablesCasing>LeaveAsIs</GlobalVariablesCasing>
      <GroupByOrderByFirstItemBreakType>Never</GroupByOrderByFirstItemBreakType>
      <InValuesFirstItemBreakType>SimpleWrap</InValuesFirstItemBreakType>
      <InValuesOpeningParenthesisAlignment>ToStatement</InValuesOpeningParenthesisAlignment>
      <InValuesOpeningParenthesisBreakType>Never</InValuesOpeningParenthesisBreakType>
      <InValuesSubsequentListItemsBreakType>SimpleWrap</InValuesSubsequentListItemsBreakType>
      <IndentBeginEndKeywords>false</IndentBeginEndKeywords>
      <IndentBlockContents>true</IndentBlockContents>
      <IndentCteContents>false</IndentCteContents>
      <IndentCteName>false</IndentCteName>
      <IndentDdlClauses>true</IndentDdlClauses>
      <IndentInsertColumnListsParenthesisedExpression>true</IndentInsertColumnListsParenthesisedExpression>
      <IndentInsertValuesListsParenthesisedExpression>true</IndentInsertValuesListsParenthesisedExpression>
      <IndentJoinTable>false</IndentJoinTable>
      <IndentListItems>true</IndentListItems>
      <IndentParenthesisedExpression>true</IndentParenthesisedExpression>
      <InsertColumnListParenthesisLayout>CompactSimple</InsertColumnListParenthesisLayout>
      <InsertEmptyLineBetweenJoins>false</InsertEmptyLineBetweenJoins>
      <InsertValuesListParenthesisLayout>CompactSimple</InsertValuesListParenthesisLayout>
      <IsMigratedStyle>false</IsMigratedStyle>
      <JoinBreakType>ChopAlways</JoinBreakType>
      <JoinClauseAlignment>Tabbed</JoinClauseAlignment>
      <JoinConditionBreakType>Never</JoinConditionBreakType>
      <JoinOnAlignment>TabbedFromJoin</JoinOnAlignment>
      <JoinOnConditionAlignment>ToOnKeyword</JoinOnConditionAlignment>
      <Name>Inntopia</Name>
      <NewLineAfterTopRowFilter>true</NewLineAfterTopRowFilter>
      <NewLineBeforeTopRowFilter>false</NewLineBeforeTopRowFilter>
      <NewLineForCorrelatedTableSource>false</NewLineForCorrelatedTableSource>
      <NumberOfEmptyLinesAfterBatchSeparator>0</NumberOfEmptyLinesAfterBatchSeparator>
      <NumberOfEmptyLinesBetweenStatements>1</NumberOfEmptyLinesBetweenStatements>
      <NumberOfSpacesInTab>4</NumberOfSpacesInTab>
      <OpeningParenthesisAlignment>ToStatement</OpeningParenthesisAlignment>
      <OpeningParenthesisBreakType>ChopAlways</OpeningParenthesisBreakType>
      <OptionsVersion>12</OptionsVersion>
      <OverrideParenthesesOptionsForCreateAlterStatements>true</OverrideParenthesesOptionsForCreateAlterStatements>
      <ParenthesisLayout>CompactSimple</ParenthesisLayout>
      <ParenthesisedExpressionAlignment>TabbedFromOpeningBracket</ParenthesisedExpressionAlignment>
      <ParenthesisedExpressionBreakType>ChopAlways</ParenthesisedExpressionBreakType>
      <PlaceBeginOnNewLine>true</PlaceBeginOnNewLine>
      <PlaceCaseElseOnNewLine>true</PlaceCaseElseOnNewLine>
      <PlaceCaseEndOnNewLine>false</PlaceCaseEndOnNewLine>
      <PlaceCaseExpressionOnNewLine>false</PlaceCaseExpressionOnNewLine>
      <PlaceCaseFirstWhenOnNewLine>IfTheresAnInputExpression</PlaceCaseFirstWhenOnNewLine>
      <PlaceClosingParenthesisOnNewLine>true</PlaceClosingParenthesisOnNewLine>
      <PlaceCommasBeforeListItems>false</PlaceCommasBeforeListItems>
      <PlaceJoinOnKeywordOnANewLine>false</PlaceJoinOnKeywordOnANewLine>
      <PlaceJoinTableOnNewLine>false</PlaceJoinTableOnNewLine>
      <PreferBreakBeforeAsKeyword>false</PreferBreakBeforeAsKeyword>
      <PreferBreakBeforeBetweenAndKeyword>false</PreferBreakBeforeBetweenAndKeyword>
      <PreferBreakBeforeBetweenKeyword>false</PreferBreakBeforeBetweenKeyword>
      <PreferBreakBeforeConditionOperator>true</PreferBreakBeforeConditionOperator>
      <PreferBreakBeforeCteName>false</PreferBreakBeforeCteName>
      <PreferBreakBeforeEquals>true</PreferBreakBeforeEquals>
      <PreferBreakBeforeInsertTable>false</PreferBreakBeforeInsertTable>
      <PreferBreakBeforeThenKeyword>false</PreferBreakBeforeThenKeyword>
      <PreserveLineSpacingBetweenStatements>true</PreserveLineSpacingBetweenStatements>
      <PreserveLineSpacingWithinStatements>false</PreserveLineSpacingWithinStatements>
      <ReservedKeywordsCasing>Uppercase</ReservedKeywordsCasing>
      <SemicolonWhitespace>None</SemicolonWhitespace>
      <SpaceBeforeUnits>false</SpaceBeforeUnits>
      <SpaceBetweenDataTypeAndParameters>false</SpaceBetweenDataTypeAndParameters>
      <SpaceInsideEmptyParentheses>false</SpaceInsideEmptyParentheses>
      <SpacesAroundArithmeticOperators>
        <m_After>true</m_After>
        <m_Before>true</m_Before>
      </SpacesAroundArithmeticOperators>
      <SpacesAroundCommas>
        <m_After>true</m_After>
        <m_Before>false</m_Before>
      </SpacesAroundCommas>
      <SpacesAroundComparisonOperators>
        <m_After>true</m_After>
        <m_Before>true</m_Before>
      </SpacesAroundComparisonOperators>
      <SpacesAroundFunctionCallArguments>
        <m_After>false</m_After>
        <m_Before>false</m_Before>
      </SpacesAroundFunctionCallArguments>
      <SpacesAroundFunctionCalls>
        <m_After>false</m_After>
        <m_Before>false</m_Before>
      </SpacesAroundFunctionCalls>
      <SpacesAroundInPredicateContents>
        <m_After>false</m_After>
        <m_Before>false</m_Before>
      </SpacesAroundInPredicateContents>
      <SpacesAroundParentheses>
        <m_After>true</m_After>
        <m_Before>true</m_Before>
      </SpacesAroundParentheses>
      <SpacesAroundParenthesesContents>
        <m_After>false</m_After>
        <m_Before>false</m_Before>
      </SpacesAroundParenthesesContents>
      <SpacesOrTabs>TabsWherePossible</SpacesOrTabs>
      <SubsequentInsertColumnsListItemsBreakType>ChopIfLong</SubsequentInsertColumnsListItemsBreakType>
      <SubsequentInsertValuesListItemsBreakType>ChopIfLong</SubsequentInsertValuesListItemsBreakType>
      <SubsequentListItemsBreakType>ChopAlways</SubsequentListItemsBreakType>
      <UseGlobalListOptionsForDmlStatements>false</UseGlobalListOptionsForDmlStatements>
      <UseObjectDefinitionCase>true</UseObjectDefinitionCase>
      <VerticallyAlignAliases>false</VerticallyAlignAliases>
      <VerticallyAlignClauseItems>false</VerticallyAlignClauseItems>
      <VerticallyAlignColumnDefinitions>true</VerticallyAlignColumnDefinitions>
      <VerticallyAlignComparisonOperators>false</VerticallyAlignComparisonOperators>
      <VerticallyAlignDataTypes>true</VerticallyAlignDataTypes>
      <VerticallyAlignJoinConditionWithJoinTable>false</VerticallyAlignJoinConditionWithJoinTable>
      <VerticallyAlignListItemComments>true</VerticallyAlignListItemComments>
      <VerticallyAlignListItems>false</VerticallyAlignListItems>
      <WhereClauseFirstItemBreakType>Never</WhereClauseFirstItemBreakType>
      <WrapColumn>200</WrapColumn>
      <WrapLongLines>true</WrapLongLines>
    </LayoutOptions>
  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    Hmm, with your style, I get this:

    IF @Country = 'USA' BEGIN
    Not sure why you get the weird formatting. I'll have to bump this.

  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    OK, I have these answers:
    =========
    This is due to the Control Flow options they have set and the example they use in the different cases.  So they have
    "Place BEGIN keyword on new line" true
    "Indent BEGIN END keywords" false
    "Indent contents of statements" true
    "Collapse statements shorter than <100> characters" true

    In the first example
    ```IF @Country = 'USA' BEGIN
    SET @FormatCode = 1;
    END;
    ELSE BEGIN
    SET @FormatCode = 2;
    END;```
    It's collapsing the statements because they are shorter than 100 characters - this overrides the Begin on new line and the indent contents of statements.

    It does something odd though if you make "Place BEGIN keyword on new line" false but leave the others - it then indents the second SET statement which I think is probably a bug.

    In their last example the content of the BEGIN/END is more than 100 chars and so it doesn't collapse it.

    This is all independent of how far the IF is indented as that will be set to the correct indention if the collapse is not set.  So if you add another BEGIN / END around the above query and turn off collapse, it will indent the content of the overall BEGIN as well as the internal BEGINs to the correct place
    ========

    I've played with the style. I think the removing the collapse item fixes most of this, but the SETs are still wrong if things are not included inside some other structure. Is this just a script you have or is it part of a larger set of work?
  • This is part of a stored procedure. I only cut out the place where RedGate SQL Prompt was failing to produce expected results. So the whole thing was indented at least once from the BEGIN / END around the entire PROC, and likely indented again since I believe it was in an IF BEGIN / END block as well.

    This also used to work as expected in a previous version of SQL Prompt, in that the statements enclosed in the BEGIN / END statements aligned with the N in the BEGIN (one tab over) Here is a sample from the last working SQL Prompt version:
    	IF dbo.DATEONLY(@EndDate) = dbo.MONTHEND_DATEONLY(@EndDate)
    	BEGIN
    		IF @StartDate IS NULL SET @StartDate = DATEADD(mm, -3, dbo.MONTHBEGIN(@EndDate));
    	END;
    	ELSE BEGIN
    			 IF @StartDate IS NULL SET @StartDate = DATEADD(dd, -1, @EndDate);
    	END;
    Well at least it looks correct in SSMS, where a tab is equivalent to 4 spaces.
  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    This is strange. I've reported as a possible bug, but it's a bank holiday in the UK today.

    The thing I notice with your setup, if I change the Wrap after setting in Control Flow to a lower value, like 70-80, I get better behavior. I'm not sure of the interaction here, but I'll try to find an answer.
  • When I format this in 9.2.8 (just released today) I get:
    IF dbo.DATEONLY(@EndDate) = dbo.MONTHEND_DATEONLY(@EndDate)
        BEGIN
            IF @StartDate IS NULL
                SET @StartDate = DATEADD(mm, -3, dbo.MONTHBEGIN(@EndDate));
        END;
    ELSE
        BEGIN
            IF @StartDate IS NULL
                SET @StartDate = DATEADD(dd, -1, @EndDate);
        END;
    Which I think is what is looking to be done?  I've attached the style and settings (extension changed to .txt so it would attach) I used where it formatted it like this.

    If I'm missing something let me know (or perhaps it was fixed in the latest version?).

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Matthew_SontumMatthew_Sontum Posts: 26 Bronze 1
    edited September 14, 2018 1:11PM
    This is still an issue in 9.2.9. Here is an example with multiple BEGIN statements:
    BEGIN
    	BEGIN
    		BEGIN
    			IF @Country = 'USA' BEGIN
    SET @FormatCode = 1;
    			END;
    			ELSE BEGIN
    SET @FormatCode = 2;
    			END;
    		END;
    	END;
    END;
    As you can see, the SET statements have no indentation at all.

    I would like to automate style checks around the style guide I posted, but until this error is addressed we cannot, since it would undo the manual fixes around this problem.
  • Hi @Matthew_Sontum ,

    I've just updated to 9.2.9 as well and I still get it formatting correctly with my settings:
    BEGIN
        BEGIN
            BEGIN
                IF @Country = 'USA'
                BEGIN
                    SET @FormatCode = 1;
                END;
                ELSE
                BEGIN
                    SET @FormatCode = 2;
                END;
            END;
        END;
    END;
    I believe this are still the settings I have attached above.

    Can you try the files I have attached above to see if it works for you there?  Keep a copy of your settings and style and if reverting to those settings and style then cause the issue to recur please attach them here (you may want to remove the list of servers to alias before doing so if you have any).

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    I have 9.2.7, and with my format, or the AB style Alex added, this formats. With Mathew's file above, this doesn't.

    Mathew, I think this is something with the various setting combinations you've chosen. As I mentioned above, if you play with the Control Flow, this seems to adjust things. I think the "Collapse" checkbox in this section causes issues with various other settings. Can you remove this?
  • Unfortunately, every object in the database (1000+) has already been formatted with the format file that I sent you, with an earlier version of RedGate SQL Prompt 9.x that didn't have this error. Since the database is under source control it isn't a trivial matter to reformat, the original formatting took weeks and caused a lot of merge conflicts.

    Please pass this issue on to the RedGate SQL Prompt support team so that they can determine which version it started breaking in, why, and determine how to fix it.
Sign In or Register to comment.