What are the challenges you face when working across database platforms? Take the survey
Options

Why is SQL Prompt adding an extra empty line between statements?

tdesanctistdesanctis Posts: 6 New member
In the formatting style I am using SQL Prompt adds an extra line between each of my statements despite my settings which are the following:

Global -- Whitespace:

Preserve existing empty lines between statements -- UNCHECKED
Empty lines between statements -- 0
Empty lines after batch separator -- 0
Preserve existing empty lines within statements -- UNCHECKED

For example, here is some code unformatted:
---------------------------------
CREATE TABLE #temp (var1 INT)

INSERT [#temp] ([var1])
VALUES
(NULL -- var1 - int
)

SELECT 1 FROM [#temp] [t]
---------------------------------
When I format the code it turns into this (notice the extra lines between each statement):
---------------------------------
CREATE TABLE [#temp] ([var1] INT);


INSERT [#temp] ([var1])
VALUES
(NULL -- var1 - int
);


SELECT 1 FROM [#temp] [t];
---------------------------------

I'm pulling my hair out because I have to go back and remove the extra line between every single statement.

Am I missing something?

Best Answer

  • Options
    tdesanctistdesanctis Posts: 6 New member
    I figured it out... I had multiple styles being applied at once.  Apparently the other one had the extra lines.  I guess I wasn't expecting that and only noticed it from the Active Style context menu when I saw both styles had a check-mark next to them.  Maybe a suggestion to inform the user they have multiple styles selected?

Answers

  • Options
    tdesanctistdesanctis Posts: 6 New member
    edited July 18, 2023 1:58PM
    Well now.. That is strange, when I posted the code, the before and after formatting above both look like they have extra spaces even though only the second set of code actually does.  Not sure if that's an issue with the forum formatting but its going to make it very difficult to see the difference.
  • Options
    Hi @tdesanctis

    Thank you for reaching out on the Redgate forums, sorry to hear about your frustration.
    I'm attempting to replicate this experience as I feel it may be a setting somewhere that is triggering this.

    Reading your 2nd post makes a bit of sense as I wasn't seeing the spacing issue correctly.

    Because of this forum modifying the code you are sharing, is it possible to share a screenshot of the code directly in SSMS so I can better visualise this and find a solution with you.

    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    tdesanctistdesanctis Posts: 6 New member
    Certainly, here you go.  Thanks for looking!

  • Options

    Can you please check in your formatting style if you have Preserve existing empty lines between statements enabled

     Formatting style > Global > Whitespace

    Preserve existing empty lines between statements > enabled/disabled

     

     

    With it enabled, code appears as per you provided it

     

    When disabled, it is adding in the empty spaces between statements. My configuration is set to 2 empty lines between statements & 3 empty lines after batch separator. 

     

     This maybe the cause of your extra spaces in the code formatting

    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    tdesanctistdesanctis Posts: 6 New member
    No good... No matter if I check or uncheck "Preserve existing empty lines between statements" it is still putting in two empty lines between each statement.  The numbers in the drop down boxes for me are both 0 as I stated at the top of the thread. I tried changing these both to 1 to see if it would make a difference but it did not.



    It should be noted that the preview is showing the correct spacing but when I actually format a query with it, its adding an extra empty line.
  • Options

    Hi @tdesanctis

     

    Thank you for sharing your solution - very interesting about the two styles activated at once.

    I think I've replicated it and may be a bug.

     

    With your two styles, was one in Your Styles & one in Shared Styles?

    I believe these should be sync'd, but it seems that you can change may be able to have two different styles applied which is not expected behaviour.

     

    If you can please confirm your two styles and if they are in the same locations I will escalate this to our development team as a possible bug.

     

    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Options
    tdesanctistdesanctis Posts: 6 New member
    No, for me they were both under "YOUR STYLES"  As soon as I deleted the one I wanted to use started working properly. Unfortunately I no longer have the other one so I cannot give you any more information than that.  But glad this helped you recreate.
  • Options
    Thanks for the info, glad it has been resolved for you in this instance.

    I'll forward this through internally to our development team to be investigated as a potential bug as I certainly don't expect two styles to be applied simultaneously. 

    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.