Format INSERT INTO...EXECUTE

ogrishman · July 12, 2016 2:26AM

This is my format settings (you can import into SQL Prompt):

&lt;?xml version="1.0" encoding="utf-8" standalone="yes"?&gt;
&lt;!----&gt;
&lt;LayoutOptions version="1" type="LayoutOptions"&gt;
  &lt;ReservedWordsCasing&gt;1&lt;/ReservedWordsCasing&gt;
  &lt;BuiltInFunctionCasing&gt;1&lt;/BuiltInFunctionCasing&gt;
  &lt;BuiltInDataTypeCasing&gt;1&lt;/BuiltInDataTypeCasing&gt;
  &lt;GlobalVariableCasing&gt;1&lt;/GlobalVariableCasing&gt;
  &lt;DelimitIdentifiers&gt;False&lt;/DelimitIdentifiers&gt;
  &lt;IndentationAmount&gt;4&lt;/IndentationAmount&gt;
  &lt;TabIndentingBehaviour&gt;0&lt;/TabIndentingBehaviour&gt;
  &lt;ReformatExpressions&gt;True&lt;/ReformatExpressions&gt;
  &lt;FirstColumnOnNewLine&gt;False&lt;/FirstColumnOnNewLine&gt;
  &lt;CommasAtStart&gt;False&lt;/CommasAtStart&gt;
  &lt;MultilineSpacesAfterCommas&gt;False&lt;/MultilineSpacesAfterCommas&gt;
  &lt;MultilineSpacesBeforeCommas&gt;False&lt;/MultilineSpacesBeforeCommas&gt;
  &lt;MultilineAlignCommasWithStatement&gt;True&lt;/MultilineAlignCommasWithStatement&gt;
  &lt;OperatorsOnNewline&gt;True&lt;/OperatorsOnNewline&gt;
  &lt;OperatorsAtStart&gt;True&lt;/OperatorsAtStart&gt;
  &lt;IndentBeginToken&gt;False&lt;/IndentBeginToken&gt;
  &lt;IndentWithExpressionBrackets&gt;True&lt;/IndentWithExpressionBrackets&gt;
  &lt;IndentWithParameterBrackets&gt;True&lt;/IndentWithParameterBrackets&gt;
  &lt;IndentWithQueryBrackets&gt;True&lt;/IndentWithQueryBrackets&gt;
  &lt;CloseBracketsOnNewLine&gt;True&lt;/CloseBracketsOnNewLine&gt;
  &lt;OpenBracketsOnNewLine&gt;True&lt;/OpenBracketsOnNewLine&gt;
  &lt;Wrap&gt;True&lt;/Wrap&gt;
  &lt;WrapWidth&gt;160&lt;/WrapWidth&gt;
  &lt;ShortLength&gt;15&lt;/ShortLength&gt;
  &lt;CreateBracketsAtStartOfLines&gt;True&lt;/CreateBracketsAtStartOfLines&gt;
  &lt;ParameterBracketOnNewline&gt;True&lt;/ParameterBracketOnNewline&gt;
  &lt;CloseParameterBracketsOnNewLine&gt;True&lt;/CloseParameterBracketsOnNewLine&gt;
  &lt;CompactShortStatements&gt;False&lt;/CompactShortStatements&gt;
  &lt;SinglelineSpacesAfterCommas&gt;True&lt;/SinglelineSpacesAfterCommas&gt;
  &lt;SinglelineSpacesBeforeCommas&gt;False&lt;/SinglelineSpacesBeforeCommas&gt;
  &lt;SpacesAroundOperators&gt;True&lt;/SpacesAroundOperators&gt;
  &lt;SpacesAroundComparisons&gt;True&lt;/SpacesAroundComparisons&gt;
  &lt;FirstParameterDefinitionOnNewLine&gt;True&lt;/FirstParameterDefinitionOnNewLine&gt;
  &lt;SpacesAfterBrackets&gt;False&lt;/SpacesAfterBrackets&gt;
  &lt;LayoutSelectStatements&gt;True&lt;/LayoutSelectStatements&gt;
  &lt;LayoutCreateStatements&gt;True&lt;/LayoutCreateStatements&gt;
  &lt;QueryNewlineAfterOpenBracket&gt;True&lt;/QueryNewlineAfterOpenBracket&gt;
  &lt;QueryNewlineBeforeCloseBracket&gt;True&lt;/QueryNewlineBeforeCloseBracket&gt;
  &lt;JoinConditionsOnNewline&gt;True&lt;/JoinConditionsOnNewline&gt;
  &lt;AlignConditionsWithJoins&gt;False&lt;/AlignConditionsWithJoins&gt;
  &lt;AlignJoinsWithFrom&gt;False&lt;/AlignJoinsWithFrom&gt;
  &lt;AlignSelectWithInsert&gt;True&lt;/AlignSelectWithInsert&gt;
  &lt;IndentByKeywordWidth&gt;False&lt;/IndentByKeywordWidth&gt;
  &lt;ColumnsOnSingleLine&gt;False&lt;/ColumnsOnSingleLine&gt;
  &lt;DoNotIndentProcedureContents&gt;True&lt;/DoNotIndentProcedureContents&gt;
  &lt;ExecuteParametersOnSingleLine&gt;False&lt;/ExecuteParametersOnSingleLine&gt;
  &lt;IndentSelectSubclauses&gt;False&lt;/IndentSelectSubclauses&gt;
  &lt;UseObjectDefinitionCase&gt;True&lt;/UseObjectDefinitionCase&gt;
  &lt;CamelCaseNameOverrides /&gt;
  &lt;FormatActionLayout&gt;True&lt;/FormatActionLayout&gt;
  &lt;FormatActionApplyCasing&gt;True&lt;/FormatActionApplyCasing&gt;
  &lt;FormatActionInsertSemicolons&gt;True&lt;/FormatActionInsertSemicolons&gt;
  &lt;FormatActionExpandWildCards&gt;False&lt;/FormatActionExpandWildCards&gt;
  &lt;FormatActionQualifyObjectNames&gt;False&lt;/FormatActionQualifyObjectNames&gt;
  &lt;FormatActionRemoveSquareBrackets&gt;False&lt;/FormatActionRemoveSquareBrackets&gt;
&lt;/LayoutOptions&gt;

I have this SQL and need to be formatted.

INSERT INTO sp_dbcc_ind EXECUTE('DBCC IND(''test'', ''test.dbo.t1'', 1)');

Based on my formatter setting, the formatted code is:

INSERT  INTO sp_dbcc_ind
        EXECUTE (
                 'DBCC IND(''test'', ''test.dbo.t1'', 1)'
               );

However, what I want is:

INSERT INTO sp_dbcc_ind
EXECUTE ('DBCC IND(''test'', ''test.dbo.t1'', 1)');

Since my formatting setting is tweaked specifically based on my needs, I do not want to change it. Is it possible to add settings that allow the EXECUTE statement to not indent and its parameter in one line?

Thanks.

Ali D · July 12, 2016 5:06AM

Hi

Sorry to hear youâ€™re having trouble with the current formatting system. Weâ€™re working on a much more powerful formatting system for SQL Prompt and so have halted work on the current formatter. Weâ€™re quite close to finishing the new one and itâ€™ll be released in the next beta.

Once itâ€™s released, itâ€™d be great if you could try it out and let us know whether it solves this issue.

Thanks
Ali

ogrishman · July 12, 2016 6:05AM

Thank you very much Ali! I can't wait to try the new beta release!

Format INSERT INTO...EXECUTE

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk