[FORMATTING] OPTION Formatting for T-SQL

[avishj]

Input data

Which SQL and options did you provide as input?

WITH
    GeneratedPageNumbers AS (
        SELECT
            @startPage AS [left_page_number],
            @startPage + 1 AS [right_page_number]
        UNION ALL
        SELECT
            [left_page_number] + 2 AS [left_page_number],
            [right_page_number] + 2 AS [right_page_number]
        FROM
            GeneratedPageNumbers
        WHERE
            [left_page_number] + 2 <= @maxPage
    )
SELECT
    gpn.[left_page_number],
    ct_lt.[title] AS [left_title],
    ct_rt.[title] AS [right_title]
FROM
    GeneratedPageNumbers gpn
    LEFT JOIN cookbook_titles ct_lt ON ct_lt.[page_number] = gpn.[left_page_number] LEFT JOIN cookbook_titles ct_rt ON ct_rt.[page_number] = gpn.[right_page_number] OPTION (MAXRECURSION 0);

Expected Output

WITH
    GeneratedPageNumbers AS (
        SELECT
            @startPage AS [left_page_number],
            @startPage + 1 AS [right_page_number]
        UNION ALL
        SELECT
            [left_page_number] + 2 AS [left_page_number],
            [right_page_number] + 2 AS [right_page_number]
        FROM
            GeneratedPageNumbers
        WHERE
            [left_page_number] + 2 <= @maxPage
    )
SELECT
    gpn.[left_page_number],
    ct_lt.[title] AS [left_title],
    ct_rt.[title] AS [right_title]
FROM
    GeneratedPageNumbers gpn
    LEFT JOIN cookbook_titles ct_lt ON ct_lt.[page_number] = gpn.[left_page_number]
    LEFT JOIN cookbook_titles ct_rt ON ct_rt.[page_number] = gpn.[right_page_number] 
OPTION 
    (MAXRECURSION 0);

Actual Output

WITH
    GeneratedPageNumbers AS (
        SELECT
            @startPage AS [left_page_number],
            @startPage + 1 AS [right_page_number]
        UNION ALL
        SELECT
            [left_page_number] + 2 AS [left_page_number],
            [right_page_number] + 2 AS [right_page_number]
        FROM
            GeneratedPageNumbers
        WHERE
            [left_page_number] + 2 <= @maxPage
    )
SELECT
    gpn.[left_page_number],
    ct_lt.[title] AS [left_title],
    ct_rt.[title] AS [right_title]
FROM
    GeneratedPageNumbers gpn
    LEFT JOIN cookbook_titles ct_lt ON ct_lt.[page_number] = gpn.[left_page_number]
    LEFT JOIN cookbook_titles ct_rt ON ct_rt.[page_number] = gpn.[right_page_number] OPTION (MAXRECURSION 0);

Usage

• How are you calling / using the library? SQL Formatter VSCode by @nene
• What SQL language(s) does this apply to? T-SQL
• Which SQL Formatter version are you using? v4.0.0

I have tested this further and the OPTION seems to be attached to whatever the last line is, even if that is not the relevant line for recursion. I think it would be better for it to format to a new line.

[avishj]

It might fit in feature requests section better, but I wanted to get the Isssue Template from bug to show current / desired formatting.

[nene]

Indeed. The OPTION should be formatted as another clause of the SELECT statement.

[nene]

The fix for this is now released in 15.0.2 (and in 4.0.2 version of the VSCode extension).