[SA Pro 11.1.107] - Code Formatting Problems

[SA Pro 11.1.107] - Code Formatting Problems

Hello,

I have been using SQL Assistant Pro for 10 years, since v4.0 and the SQL Code Formatting has always been fantastic.

I use Microsoft SQL Server Management Studio v17.9 (14.0.17285.0) and SQL Server.

However, ever since 11.0 came out (and it still isn't fixed in the 11.1.107 released today), I have had many problems with code formatting not working.

First, specifically, when SA formats stored procedure parameters, the first parameter is now always left justified, and I have to manually [tab] it over to be in line with the other parameters. This is true whether there is only one parameter, or multiple. Here are both examples:

Second, many times, the FROM and WHERE clauses are garbled and all left justified and lines incorrectly split.

Here is the complete text of one stored procedure that exhibits both issues so that you can test. If this is some sort of settings issue, then I don't know what it is because I haven't changed any settings in quite some time. I upgraded from 10 to 11 and now to 11.1.107 and the settings are imported from the previous version automatically. Maybe since I have used SA for so many years, there are "old" incompatible settings lingering from past version that are causing this? Don't know. But have never had this kind of formatting problem until 11.

Thanks for your help.

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspReport_MailingList_Members_AllActive]
@MonthID INT
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;

DECLARE @DefaultEmptyDate DATETIME = DATEFROMPARTS(1800, 1, 1)
DECLARE @DefaultMaxDate DATETIME = DATEFROMPARTS(5555, 12, 31)
DECLARE @ExpireDateLastYear DATETIME = DATEFROMPARTS(YEAR(GETDATE()) -1, 12, 31)

SELECT m.MembershipNo MemberNo,
   m.FirstName,
   m.MiddleName,
   m.LastName,
   ma.Address1,
   ma.Address2,
   ma.City,
   ma.[State] StateProvince,
   ma.Country,
   ma.PostalCode,
   CASE
   WHEN m.[ExpireDate] = @DefaultMaxDate THEN 'LIFE'
   ELSE CAST(YEAR(m.[ExpireDate]) AS VARCHAR(5))
   END PaidTill,
   CASE
   WHEN m.Phone1Type = 'H' THEN m.Phone1
   WHEN m.Phone2Type = 'H' THEN m.Phone2
   WHEN m.Phone3Type = 'H' THEN m.Phone3
   END HomePhone,
   CASE
   WHEN m.Phone1Type = 'M' THEN m.Phone1
   WHEN m.Phone2Type = 'M' THEN m.Phone2
   WHEN m.Phone3Type = 'M' THEN m.Phone3
   END MobilePhone,
   m.Email,
   mt.TypeAbbrev MemberType,
   ms.StatusAbbrev MemberStatus,
   mst.MembershipAbbrev MembershipType,
   m.[ExpireDate],
   m.iMemberType OldType,
   m.iStatus OldStatus,
   CASE
   WHEN ISNULL(m.AlternateAddressID, 0) > 0
   AND ISNULL(m.AlternateAddressBeg, 0) > 0
   AND ISNULL(m.AlternateAddressEnd, 0) > 0 THEN CASE
   WHEN @MonthID >= m.AlternateAddressBeg OR @MonthID =
   m.AlternateAddressEnd THEN 'SNOWBIRD'
   ELSE 'PRIMARY'
   END
   ELSE 'PRIMARY'
   END AddrType,
   CASE ma.IsBad
   WHEN 1 THEN 'Yes'
   ELSE 'No'
   END AddrIsBad
FROM dbo.Member m
   LEFT JOIN dbo.MemberAddress ma
   -- ==============================
   -- Dynamically link to the correct mailing address based on member
   -- having a defined Snowbird address for the month specified
   -- ==============================
   ON ma.MemberAddressID = CASE
   WHEN ISNULL(m.AlternateAddressID, 0) > 0
   AND ISNULL(m.AlternateAddressBeg, 0) > 0
   AND ISNULL(m.AlternateAddressEnd, 0) > 0 THEN CASE
   WHEN @MonthID >= m.AlternateAddressBeg OR @MonthID =
   m.AlternateAddressEnd THEN m.AlternateAddressID
   ELSE m.PrimaryAddressID
   END
   ELSE m.PrimaryAddressID
END
LEFT JOIN dbo.MemberType mt
ON mt.MemberTypeID = m.MemberTypeID
LEFT JOIN dbo.MemberStatus ms
ON ms.MemberStatusID = m.MemberStatusID
LEFT JOIN dbo.MembershipType mst
ON mst.MembershipTypeID = m.MembershipTypeID
WHERE m.MembershipNo > 0
AND m.ParentMemberID = 0
AND m.PreviousMemberID = 0
AND m.MemberTypeID
IN (1, 5)
AND m.[ExpireDate] > @ExpireDateLastYear
AND m.MemberStatusID
IN (1)
AND m.MembershipTypeID NOT
IN (0, 10, 11, 14)
AND ma.IsBad = 0
ORDER BY
ma.[State],
ma.PostalCode
END

FYI - I had to re-post the code 4 times because apparently the forum "code" block can't handle a "less than or equal to" (less than character and equal character) and thinks it is html, and garbles the rest of the code.

So, I just removed the less than signs from the statement.

Thanks.

Could you post the formatting rule for alter procedure and for select, please?

Yes. Here are the rules. I am pretty sure they are the default as I have changed very few options here.

Thank you for reporting this issue. Can you please email the complete file you're trying to format to our support account (available on the About tab in the Options) so that we can test it in full?

Both issues appear to be reproducible with default settings. We are actively investigating them.

We have a fix for both issues available. We can provide a private build for testing sometime next week.

When are these fixed going to be available for the rest of us?

I'm fairly sure you can ask the support for a link.

Hi,

The previously referenced build containing code formatting engine changes is now available and we think the reported issues have been fixed. If you are interested in trying it, please email to support and we will gladly send you a download link.

	Mogensen wrote:
	When are these fixed going to be available for the rest of us?

The latest changes in the code formatting engine are still being tested for accuracy and regressions. You are very welcome to join and provide your input. Please email to support and ask for download link for 11.1.110. This will allow you to try the updates with your code and scripts locally and verify it works well for you. If you come across any issues, or the results are not satisfying enough, please let us know and we will address them as quickly as possible. We need your input.