How can we help you today? How can we help you today?

Formatting OFFSET...FETCH NEXT

I am encountering an issue with SQL Prompt’s formatting of queries that use OFFSET...FETCH. Specifically, SQL Prompt incorrectly removes the space between ORDER BY and OFFSET, making the query invalid.

For example, given the following SQL query:

SELECT ROW_NUMBER () OVER ( ORDER BY Name ) AS 'RowNumber',  
      name,  
      listprice  
FROM Production.Product  
ORDER BY Name  
OFFSET 20 ROWS  
FETCH NEXT 10 ROWS ONLY;  

SQL Prompt reformats it as:

SELECT ROW_NUMBER () OVER ( ORDER BY Name ) AS 'RowNumber',  
      name,  
      listprice  
FROM Production.Product  
ORDER BY Name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  

This formatting issue creates SQL that is less readable than the earlier sample and there is no option that allows us to specify that these clauses (OFFSET, FETCH) should begin on a new line and follow the alignment rules that other clauses do. 

I would appreciate any guidance on resolving this, whether through adjustments in formatting settings or a future patch.

Please let me know if further details are needed.

LiveToCodeAnotherDay
0

Comments

1 comment

Sort by
  • LiveToCodeAnotherDay

    To clarify, the first paragraph is incorrect. It should read:

    I am encountering an issue with SQL Prompt’s formatting of queries that use OFFSET...FETCH. Specifically, SQL Prompt places the OFFSET at the end of the ORDER BY, producing less readable code that deviates from common SQL formatting conventions and (more notably, our corporate standards).

    LiveToCodeAnotherDay
    0

Add comment

Please sign in to leave a comment.