SQL Prompt is not formatting IF statement correctly

The following SQL is not formatted correct.

IF @CalculateHeld = 'Yes' BEGIN
SELECT * FROM [#HeldFunds] WHERE [HeldAmount] > 0
DROP TABLE [#HeldFunds]
END ELSE BEGIN
SELECT * FROM [#HeldFunds2] WHERE [HeldAmount] > 0
DROP TABLE [#HeldFunds2]
END

--
Brien King
[email protected]

Answers

  • Do you mean that everything inside the BEGIN and END blocks has no indentation? I have been seeing this as well.
  • Correct
    --
    Brien King
    [email protected]
  • When you say its not formatting "correctly" - how do you think it should be being formatted?

    If I use default_lowercase on 9.2.9 I get this result with indentations:


    If you're not on the latest version trying updating and test again.
  • I am on the latest (9.2.9.6459) and it wasn't formatting correctly on the previous version.  In the previous version it was basically the opposite problem where everything was spread way out.

    In this version everything is all left justified.

    This is what is looks like after being formatted:

    IF @CalculateHeld = 'Yes' BEGIN
    SELECT * FROM [#HeldFunds] WHERE [HeldAmount] > 0
    DROP TABLE [#HeldFunds]
    END ELSE BEGIN
    SELECT * FROM [#HeldFunds2] WHERE [HeldAmount] > 0
    DROP TABLE [#HeldFunds2]
    END

    What it should look like is this:

    IF @CalculateHeld = 'Yes' BEGIN
    ...SELECT * FROM [#HeldFunds] WHERE [HeldAmount] > 0
    ...DROP TABLE [#HeldFunds]
    END ELSE BEGIN
    ...SELECT * FROM [#HeldFunds2] WHERE [HeldAmount] > 0
    ...DROP TABLE [#HeldFunds2]
    END


    --
    Brien King
    [email protected]
  • Are you using a default style or a custom style?
  • With my style, I get this:

    IF @CalculateHeld = 'Yes'BEGIN    SELECT *    FROM [#HeldFunds]    WHERE [HeldAmount] > 0;    DROP TABLE [#HeldFunds];END;ELSEBEGIN    SELECT *    FROM [#HeldFunds2]    WHERE [HeldAmount] > 0;    DROP TABLE [#HeldFunds2];END;

    What I'd suggest is look at the whitespace and the control flow sections. Watch out for checking the "wrap if longer than" items. I've had some strange behavior here when these are applied with certain values.
Sign In or Register to comment.