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

2 comments

Sort by
  • Jon Kirkwood
    Official comment

    Hi LifeToCodeAnotherDay, Thank you for reaching out on the Redgate forums regarding your SQL Prompt query. I can see that there is not currently any handling for OFFSET/FETCH clauses and it will automatically append it to the previous line with standard formatting styles. Having a look on our UserVoice site I did see an open feature request asking for similar functionality to what you have outlined here; I would first ask if you can view this request and add your vote + any comments on how the lack of this functionality impacts your workflow. I can also assist by escalating this internally with our development teams to see if the feature request can be implemented into SQL Prompt in a future release. UserVoice link - https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/36307462-add-formatting-support-for-offset-and-fetch-next

    Jon Kirkwood
  • 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.