How is SQL Prompt helping your team? Share your experience.

Formatting of ELSE BEGIN is off

I'd like to report a bug in SQL Prompt's formatting style application.

An example SQL code snippet (in the format I'd like it to be)

DECLARE @isShortName BIT;

BEGIN;
    SET @isShortName = 1;
END;
ELSE
BEGIN;
    SET @isShortName = 0;
END;

However, what I'm seeing when I format my code with SQL Prompt is a little different in the ELSE clause:

DECLARE @isShortName BIT;

BEGIN;
    SET @isShortName = 1;
END;
ELSE BEGIN;
SET @isShortName = 0;
END;

Some relevant options in my formatting style:
- Control Flow:
  - Place BEGIN keyword on new line = true
  - Indent contents of statements = true
  - Collapse statements shorter than *160* characters

What I believe is happening is that I've asked to put BEGIN on its own line, but SQL Prompt is then deciding to collapse "ELSE BEGIN;\nSET @isShortName = 0;\nEND;" as if it were a statement, but then formats it with line breaks anyway. That "statement" is 38 chars long, and if I reduce "Collapse statements shorter than" to less than 39, this behaviour disappears and the the script is formatted as per the top example.

In short: SQL Prompt appears to be considering the ELSE ... END section as a statement which can be collapsed, but it should not, because it's not a single statement.

A workaround for this in the meantime is to disable the collapsing of short control flow statements, but I'd like to have true single statement conditionals (like "IF (@isShortName = 1) RETURN 0") on a single line.

Answers

  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 140 Silver 1
    edited December 13, 2021 9:25AM

    Hi Philip,


    Thank you for reaching out on the Redgate forums regarding your formatting concern with SQL Prompt.


    I have replicated this concern and do share your concern with the behaviour when collapsing IF/ELSE statement pairs.

    The behaviour on the ELSE segment is not the same as the IF statement when they should arguably have the same behaviour. 


    I am in the process of documenting this for our development team to investigate as a potential bug and will reply to this post with an update. I expect this to be later this week.


    ​​

    Jon Kirkwood | Technical Support Engineer | Redgate Software
  • Jon_KirkwoodJon_Kirkwood Brisbane, Australia Posts: 140 Silver 1
    Hi Phillip, 

    This has been identified as a low priority bug and has not been entered into the developer's current backlog.
    Should this bug be fixed it will be included as part of a future release of SQL Prompt.

    Apologies I am unable to give a more direct resolution to your post, but thank you for providing details of this bug and replication steps so we could get it reported in our system.
    Jon Kirkwood | Technical Support Engineer | Redgate Software
Sign In or Register to comment.