CTE not formatting correctly.

tshad

There are actually 3 issues for this example

If you have the following:

CREATE PROC test
@Var1 int,
@Var2 varbinary(50)
AS

-- read Dealer table

;WITH MyDealerCTE
AS
(
SELECT * FROM dbo.Dealer
)
SELECT * FROM MyDealerCTE

If I now format it I get the following:

CREATE PROC test
       @Var1 int ,
       @Var2 varbinary(50)
AS -- read Dealer table

;
WITH    MyDealerCTE
          AS ( SELECT   *
               FROM     dbo.Dealer
             )
     SELECT *
     FROM   MyDealerCTE

1) The formatting always moves the semi-colon to the line before. Not how it is normally done and I like having having the semicolon on the same line. You can accidently add a line after the semi-colon and before the "With", which would make the code invalid.

2) The comment is moved next to the "AS" statement which makes it look like that the comment is for the "AS" statement and not the block of code that follows.

If I change the code to:

CREATE PROC test
@Var1 int,
@Var2 varbinary(50)
AS

SELECT * FROM dbo.Dealer

;WITH MyDealerCTE
AS
(
SELECT * FROM dbo.Dealer
)
SELECT * FROM MyDealerCTE

You get this:

CREATE PROC test
       @Var1 int ,
       @Var2 varbinary(50)
AS
SELECT  *
FROM    dbo.Dealer;
    WITH    MyDealerCTE
              AS ( SELECT   *
                   FROM     dbo.Dealer
                 )
         SELECT *
         FROM   MyDealerCTE

1) The problem here is that you lose all your white space. Everything is jammed together. I like to have a blank line after the "AS" statement so it is apparent where the code starts. Not sure why you would also take out the blank between the "Select..." and the CTE statement.

2) This is also similar to the CTE semi-colon issue above. Here the semi-colon is above the CTE statement and at the end of the "Select..." statement. The issue is even more apparent here. You can easily add a line of code between the "Select..." and the CTE (error).

3) Why is the CTE indented here but not in the previous example. I wouldn't want it indented as it is a new statement.

Thanks,

Tom