how to avoid expansion of begin end blocks with sql formatting

GNDZ

I have a query with multiple begin and end blocks in SQL. I can hide the content by simply clicking on the '+' button, and it collapses. However, every time I run a SQL formatting, the content expands again, which is quite frustrating. Is there a solution to prevent this from happening? I hope this issue can be resolved quickly.

Jon_Kirkwood

Hi @GNDZ

 

Thank you for reaching out on the Redgate forums regarding your SSMS outline expansion question.

 

From what I can see, the outlining process to collapse statement blocks is handled by SSMS's Intellisense and not within SQL Prompt.

 

SQL Prompt does appear to expand collapsed statements that have any formatting changes applied within them.

Are you finding that you are getting expansions of code that are not having any formatting changes applied?

 

 

It does not appear to be any settings within SQL Prompt that modify the outlining behaviour, and it is currently anticipated that it will continue to expand blocks of code.

I found a few feature requests on our UserVoice page connected to this area of SSMS. 

 

As a suggested next step; I'd recommend voting against any of the existing ideas or logging a new one to outline how this impacts your usage of SSMS + SQL Prompt.

https://redgate.uservoice.com/forums/94413-sql-prompt?query=outlining

If you do leave any votes and comments, please advise, and I can conduct an escalation to our development team to raise awareness on the request.

 

jeffreestar

Some editors might have settings that control the behavior of code folding and formatting. Look through the settings or preferences for options related to code folding or auto-formatting. There might be an option to preserve code folding state.

graded

Use a SQL formatting tool that respects code folding, like the built-in SSMS formatter, Red Gate SQL Prompt, or SapphireSteel Emacs SQL mode, can help prevent this problem. Give one of those a try.

donna1205

If this behavior significantly impacts your Worldle workflow, consider reaching out to the support team for the SQL editor or formatter you’re using. Developers may prioritize fixing such usability issues if enough users request it.