Red Gate forums :: View topic - CTE not formatting correctly.
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

CTE not formatting correctly.

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
tshad



Joined: 02 Feb 2009
Posts: 9

PostPosted: Mon Oct 21, 2013 10:27 pm    Post subject: CTE not formatting correctly. Reply with quote

There are actually 3 issues for this example

If you have the following:
Code:

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:

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:

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:

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group