Error when formatting otherwise valid SQL with subqueries

opc.three

Repro:

USE tempdb;

CREATE TABLE #tmp (id INT, name VARCHAR(100))
CREATE TABLE #tmp2 (id INT, name VARCHAR(100))

DELETE  FROM #tmp
WHERE   id = (
              SELECT    id
              FROM      #tmp2
              WHERE     name = 'abc'
             )
        AND name = 'xyz';

The above is highlighted with errors when trying to format in version 7.0.0.62

opc.three

Sorry, in my attempt to cleanse my actual code to provide repro code I neglected to take out the schema name. That said, the problem persists without the schema name. Please try it out with my version.

USE tempdb;

CREATE TABLE #tmp (id INT, name VARCHAR(100))
CREATE TABLE #tmp2 (id INT, name VARCHAR(100))

DELETE  FROM #tmp
WHERE   id = (
              SELECT    id
              FROM      #tmp2
              WHERE     name = 'abc'
             )
        AND name = 'xyz';

My style document:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!---->
<LayoutOptions version="1" type="LayoutOptions">
  <ReservedWordsCasing>1</ReservedWordsCasing>
  <BuiltInFunctionCasing>1</BuiltInFunctionCasing>
  <BuiltInDataTypeCasing>1</BuiltInDataTypeCasing>
  <GlobalVariableCasing>0</GlobalVariableCasing>
  <DelimitIdentifiers>False</DelimitIdentifiers>
  <IndentationAmount>4</IndentationAmount>
  <TabIndentingBehaviour>0</TabIndentingBehaviour>
  <ReformatExpressions>True</ReformatExpressions>
  <FirstColumnOnNewLine>False</FirstColumnOnNewLine>
  <CommasAtStart>False</CommasAtStart>
  <MultilineSpacesAfterCommas>False</MultilineSpacesAfterCommas>
  <MultilineSpacesBeforeCommas>False</MultilineSpacesBeforeCommas>
  <MultilineAlignCommasWithStatement>True</MultilineAlignCommasWithStatement>
  <OperatorsOnNewline>True</OperatorsOnNewline>
  <OperatorsAtStart>True</OperatorsAtStart>
  <IndentBeginToken>False</IndentBeginToken>
  <IndentWithExpressionBrackets>True</IndentWithExpressionBrackets>
  <IndentWithParameterBrackets>True</IndentWithParameterBrackets>
  <IndentWithQueryBrackets>True</IndentWithQueryBrackets>
  <CloseBracketsOnNewLine>True</CloseBracketsOnNewLine>
  <OpenBracketsOnNewLine>True</OpenBracketsOnNewLine>
  <Wrap>False</Wrap>
  <WrapWidth>160</WrapWidth>
  <ShortLength>15</ShortLength>
  <CreateBracketsAtStartOfLines>True</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>False</JoinConditionsOnNewline>
  <AlignConditionsWithJoins>False</AlignConditionsWithJoins>
  <AlignJoinsWithFrom>False</AlignJoinsWithFrom>
  <AlignSelectWithInsert>False</AlignSelectWithInsert>
  <IndentByKeywordWidth>False</IndentByKeywordWidth>
  <ColumnsOnSingleLine>False</ColumnsOnSingleLine>
  <DoNotIndentProcedureContents>True</DoNotIndentProcedureContents>
  <ExecuteParametersOnSingleLine>False</ExecuteParametersOnSingleLine>
  <IndentSelectSubclauses>False</IndentSelectSubclauses>
  <UseObjectDefinitionCase>True</UseObjectDefinitionCase>
  <CamelCaseNameOverrides />
  <FormatActionLayout>True</FormatActionLayout>
  <FormatActionApplyCasing>True</FormatActionApplyCasing>
  <FormatActionInsertSemicolons>True</FormatActionInsertSemicolons>
  <FormatActionExpandWildCards>False</FormatActionExpandWildCards>
  <FormatActionQualifyObjectNames>True</FormatActionQualifyObjectNames>
  <FormatActionRemoveSquareBrackets>True</FormatActionRemoveSquareBrackets>
</LayoutOptions>

Andrew P

Hey Opc,

Thanks for the update!

SQL Prompt does not underline errors, that is done directly by SSMS' Intellisense. Sometimes these errors keep Prompt from doing something (like executing a single line), but that is because we take the error as something that needs to be fixed.

In this case it looks like SSMS is not detecting that the #tmp table has the columns "id" and "name" even though it was created directly above. If you put the table (#tmp) name in front of the column names (qualifying it), the tool will properly suggest a column.

If you try to use "Qualify Object names" it will error out because there are two tables that have the "id" and "name" column names and it will not (and cannot) guess which one you mean.

I hope that helps!

Warm Regards,

opc.three

AndrewPierce wrote:

SQL Prompt does not underline errors, that is done directly by SSMS' Intellisense.

Hi again Andrew. I think the wires are getting crossed somehow. The underlining (or highlighting as SQL Prompt calls it) occurs after I attempt to format the code using SQL Prompt, e.g. pressing Ctrl+K, Ctrl+Y. The underline is a wavy red line and the error message is exactly:

SQL Prompt was unable to complete this operation.
Problem areas have been highlighted.

To confirm, I have SSMS Intellisense disabled when this occurs.

I hope this clarifies the issue.

Hopefully this issue is not limited to my machine. If you would try formatting my repro code with my style settings using my version of SQL Prompt hopefully you can see what I see.