SQL Prompt is not formatting IF statement correctly

BrienKing

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

Matthew_Sontum

Do you mean that everything inside the BEGIN and END blocks has no indentation? I have been seeing this as well.

BrienKing

Correct

Russell D

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.

BrienKing

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

Russell D

Are you using a default style or a custom style?

way0utwest

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.