SQL Prompt - strange behavior when formatting 'CROSS APPLY'

Guenter

When using SQL Prompt to format my code, it seems to mess up the indentation each time I use 'CROSS APPLY' or 'OUTER APPLY', as you can see in this example:

SELECT *
FROM
  sys.[objects]
  CROSS APPLY (
    SELECT
      'this column intentionally left blank' AS [blank_column]
     ,CAST(NULL AS INT)                      AS blank_int_column
  ) x;</code>SELECT *
FROM
  sys.[objects]
  CROSS APPLY (
  SELECT
    'this column intentionally left blank' AS [blank_column]
   ,CAST(NULL AS INT)                      AS [blank_int_column]
) x;</pre><div>IMHO everything below the line "CROSS APPLY (" should be indented by one tab (being 2 spaces in my settings) more than it is.<br><br>How can I adjust my settings so the code reads<br><pre class="CodeBlock"><code>

?

Guenter

The code section seems to have messed up; hiere comes a prettier version:

SELECT *
FROM
  sys.[objects]
  CROSS APPLY (
  SELECT
    'this column intentionally left blank' AS [blank_column]
   ,CAST(NULL AS INT)                      AS blank_int_column
) x;

-- version that I'd prefer
SELECT *
FROM
  sys.[objects]
  CROSS APPLY (
    SELECT
      'this column intentionally left blank' AS [blank_column]
     ,CAST(NULL AS INT)                      AS blank_int_column
  ) x;

-- version that SQL Prompt produces

Jon_Kirkwood

Hi @Guenter

 

Thank you for reaching out on the Redgate forums, and thank you for the formatted code, it can get a bit finicky on the forums to share code snippets like this where the formatting is important.

 

I believe I understand this formatting inquiry and do think there is something peculiar about it.

 

This piece of code should likely be formatted by the Global > Parentheses formatting style.

 

The parentheses indenting is taking its value from the FROM statement.

Setting this to the 'Expanded, right aligned' style looks to be the closest available option to your request of the SELECT statement indenting against the CROSS APPLY

 

 

I will escalate this to our developers to ascertain if it is expected behaviour for the indentation to be based on the FROM statement or if the CROSS APPLY statement should be used instead.

 

Will update this forum post with their findings and any other possible workarounds

 

Jon_Kirkwood

I have received an update from our dev team who have looked into the behaviour of CROSS APPLY, and they believe this is working as intended.

 

In regard to how the parentheses and CROSS APPLY statement are aligned from the FROM clause in this query - since the statement that the parser returns is a QueryDerivedTable, the method that determines which token to align to follows this flow chart:

 

 Is there a JOIN? > is there a USING? > is there a FROM? > Select first non comment. 

Neither of the first two conditions are met, so it uses FROM as the anchor. 

Since there is no CROSS APPLY specific rule provider, the indentation stay based on FROM.