CTE not formatting correctly.

Provides intelligent code completion for SQL Server editors.

CTE not formatting correctly.

Postby tshad » Mon Oct 21, 2013 10:27 pm

There are actually 3 issues for this example

If you have the following:
Code: Select all
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:
Code: Select all
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:

Code: Select all
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:

Code: Select all
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
tshad
 
Posts: 10
Joined: Mon Feb 02, 2009 6:45 pm

Return to SQL Prompt 6

Who is online

Users browsing this forum: Bing [Bot] and 0 guests

cron