Formatting requests - post them here

Refactors and formats SQL code.

Moderators: Chris Auckland, David Atkinson, Anu Deshpande, Luke Jefferson

Re:

Postby Andras » Mon Dec 11, 2006 1:02 pm

vudang wrote:I'm not sure if this is available as an option but I cannot get sql refactor to format the insert column list on one line when using insert select. Basically the following is not possible.

INSERT INTO #extract_tlot
(
entity_name, id_1,id_2,id_3
)
SELECT DISTINCT
tl.entity_name,tl.id_1,tl.id_2,tl.id_3
FROM sometable


Hi,
this one is actually a bug, and this will be fixed in version 1.1. I will also contact you concerning this in a private message. Many thanks,
Andras
András Belokosztolszki, PhD
Red Gate Software Ltd.
Andras
 
Posts: 249
Joined: Thu May 19, 2005 5:07 pm
Location: Cambridge, UK

Trailing Spaces

Postby obbyyoyo » Wed Feb 14, 2007 7:47 pm

This is a nice-to-have kind of thing that would comfort the compulsive side of me.

I would like to always have trailing spaces removed, or at least have it as an option.

simplistic example:

before:
Code: Select all
IF 1 = 1 BEGIN SELECT t.* FROM dbo.Foo WHERE t.bar = 'zee' END ELSE BEGIN SELECT t.* FROM dbo.Zee WHERE t.bar = 'foo' END


after:
Code: Select all
IF 1 = 1 -- <-- there is a trailing space here
  BEGIN
    SELECT
      t.*
    FROM
      dbo.Foo
    WHERE
      t.bar = 'zee'
  END
ELSE  -- <-- there is a trailing space here
  BEGIN
    SELECT
      t.*
    FROM
      dbo.Zee
    WHERE
      t.bar = 'foo'
  END
obbyyoyo
 
Posts: 2
Joined: Wed Feb 14, 2007 7:38 pm

Blocks

Postby Lamprey » Thu Feb 22, 2007 9:46 pm

I know there are many ways to indent/format blocks of SQL. Specifically, the BEGIN-END blocks. I noticed someone liked the BEGIN at the end of the previous statement like:

Code: Select all
IF (x > 1) BEGIN


That aside, I would like to see an option whether or not to indent (Assuming I did not miss this feature). So, the option would work like this:

Option 1 (No Indent)
Code: Select all
BEGIN
    SELECT
        *
    FROM
        foo
END


Option 2 (With Indent)
Code: Select all
    BEGIN
        SELECT
            *
        FROM
            foo
    END


Cheers!
Lamprey
 
Posts: 3
Joined: Thu Feb 22, 2007 9:37 pm

Expression issue

Postby Lamprey » Thu Feb 22, 2007 10:05 pm

One other thing I noticed is a potential problem formatting expressions that contain operators. I have my option set to place a space around operators so that things are spaced out a bit. Because of this setting it changes a statement like:

Code: Select all
RAISERROR ('Error', -1, -1)


to one with spaces after the operator:

Code: Select all
RAISERROR ('Error', - 1, - 1)


I realize it is only whitespace, but I thought I'd mention it.
Lamprey
 
Posts: 3
Joined: Thu Feb 22, 2007 9:37 pm

Postby SvdSinner » Tue Feb 27, 2007 4:26 pm

I prefer to indent everything except for the initial line of a statement so that it is clear where statements begin/end. Examples:
Code: Select all
SELECT *
    FROM table
    WHERE col1 = whatever

Of course that example is fairly trivial, but it really gets useful in cases like this:
Code: Select all
INSERT table
    (col1, col2, col3)
    SELECT column1, column2, column3
        FROM table2
        WHERE condition = true

Much easier to read, IMHO. The standard form (aligning all clauses) leaves you wondering if SELECT statements are standalone or part of a larger query. This makes is simpler to determine at a glance what belongs to what.
SvdSinner
 
Posts: 11
Joined: Tue Feb 27, 2007 4:00 pm
Location: North of my cattle barn, Ames, IA

Indent body of transaction

Postby mbuis » Thu Apr 12, 2007 2:23 pm

I would like the option to indent the body of a transaction:

Code: Select all
SELECT ...
BEGIN TRAN
    UPDATE ...
    UPDATE ...
    IF (@@ERROR = 0)
        COMMIT
    ELSE
        ROLLBACK
SELECT ...


BEGIN TRAN would increase the indent level. Knowing when to decreasing it would be a but tricky to determine, as there may be conditional logic involved.
mbuis
 
Posts: 3
Joined: Mon Nov 20, 2006 7:36 pm

Location of Variable Type Definitions & Select Statement

Postby aaronkool29 » Thu Apr 12, 2007 2:30 pm

Changed to the Following *(Note the Periods are just for Formatting on this post, those would appear as spaces in SQL)
DECLARE
@lProcedureName .VARCHAR(100),
@lReturnCode .......INTEGER
@lValue .................INTEGER

SELECT
@lProcedureName. = 'TestProcedure,
@lReturnCode....... = 0,
@lValue................. = 123
aaronkool29
 
Posts: 2
Joined: Mon Mar 12, 2007 6:25 pm

Case sensitivity and squared brackets

Postby ad2000 » Fri Apr 13, 2007 10:30 am

Hello!

I am testing SQL Refactor and I suggest two more features:

1. I would prefer that the object names and column names would be formatted with case sensitivity.

2. And I would like to have the squared brackets set automatically to object and column names like SELECT [EmployeeID], [Title], [NationalIDNumber] FROM [HumanResources].[Employee];

Thank you!
ad2000
 
Posts: 1
Joined: Fri Apr 13, 2007 10:08 am

Formatting object names with case sensitivity

Postby Bodhi » Fri Apr 13, 2007 4:21 pm

The run time performance is better when the object names are qualified by the schema name AND the object name case is correct. So, formatting with case correction would be a valuable feature.
Bodhi
 
Posts: 6
Joined: Wed Nov 22, 2006 7:01 pm
Location: Spokane, Washington

Layout as per Microsoft Help Files

Postby GaryJF » Sun May 20, 2007 2:38 am

As a consultant, I have been to many different companies and I have noticed that quite a few of them use the defacto Microsoft layout, i.e. the layout you find in the help files.

You cannot achieve this layout with the current version of Refactor, which is a shame. Although, this is unsurprising considering the many layouts being utilised in the industry, I can see a much wider audience making use of it if it was able to layout the code in the same format as the help files.

Probably the most imortant changes to assist in this would be:
1. The ability to align comments with the next line of code
2. The ability to have BEGIN END blocks align directly under the keyword it serves
3. The ability to list colums horizontally in SELECT statements, yet vertically in modification statements (UPDATE and INSERT)
4. The ability to decide which keyword types should be uppercased (for example, uppercase all except datatypes)

There are a few others, which can easily be spotted by copying SQL Server example code and laying it out to see the changes.

If Refactor ever becomes as flexible as SQL Prompt, then it would be an invaluable asset in laying out the code. As for now, I use refactor solely for its other features, like Smart Rename, Script Summary etc. I have just purchased both the SQL Compare Bundle and the SQL Prompt Bundle, so I am a big fan of Red-Gate tools...but I know I could encourage many more of my clients to invest in the SQL Prompt Bundle when the layout feature matches the flexibility of Prompt!
The client doesn't know what he wants, until he doesn't get it!
GaryJF
 
Posts: 5
Joined: Thu May 17, 2007 11:03 am
Location: N. Ireland

Hanging Indent

Postby Wyatt70 » Thu May 24, 2007 6:43 pm

Why do the statements following FROM, JOIN, ON use a "hanging indent"?

For example:

Code: Select all
FROM
    [TableName] AS [TableNameAlias]
INNER JOIN [TableName1] AS T1
ON
    T1.TableID = T2.TableID
INNER JOIN [TableName2] AS T2
ON
    T2.Column = T1.Column;


I would like to be able to display it this way:

Code: Select all
FROM [TableName] AS [TableNameAlias]
INNER JOIN [TableName1] AS T1
ON T1.TableID = T2.TableID
INNER JOIN [TableName2] AS T2
ON T2.Column = T1.Column;
Wyatt70
 
Posts: 36
Joined: Fri Jul 29, 2005 6:17 pm
Location: Yonkers, NY

exec sp layout

Postby rx400h » Thu May 24, 2007 9:28 pm

is this possible to layout like this

exec dbo.spS
@xdoc=@xdoc
, @xmldata = @xmldata

Currently it is laying out like this


exec dbo.spS @xdoc=@xdoc, @xmldata = @xmldata
R K
rx400h
 
Posts: 2
Joined: Thu May 24, 2007 9:13 pm

Postby Giggles220 » Fri May 25, 2007 5:30 pm

Out of the listed suggestions I'd like to see the following:

- Spaces before keywords
- Nested Joins to show join order
- align commas with columns
- indent line comments with next or previous line
- remove trailing spaces
- object name and column name formated with case sensitivity

- Ability to select upper or lower case for certain objects (for example I like my commands to be in upper case but my objects maybe to be lower case)
Giggles220
 
Posts: 114
Joined: Thu Dec 07, 2006 9:06 pm
Location: Albany, NY

JOIN layout and tabs vs spaces

Postby Viking Geek » Sat May 26, 2007 7:11 am

Rather than:

Code: Select all
SELECT
    *
FROM
    HumanResources.Employee AS e
INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE
   c.ContactID = 25
ORDER BY
    c.LastName


I would prefer to have the joined table and condition on a new line, like this:

Code: Select all
SELECT
    *
FROM
    HumanResources.Employee AS e
INNER JOIN
    Person.Contact AS c ON e.ContactID = c.ContactID
WHERE
   c.ContactID = 25
ORDER BY
    c.LastName


I think that is more consistent with the layout, as major keywords are alone on each line.

I would also prefer to use tabs over spaces for indents.
Viking Geek
 
Posts: 1
Joined: Sat May 26, 2007 7:04 am

Postby Lumbago » Tue Jun 12, 2007 9:24 am

I would *very much* appreciate the possibility to force an indentation to be what you have set it to be. I have set my indentation setting to insert tabs as spaces and one tab = 2 spaces and to wrap text at 160 characters. However lay out sql gives me these to statements which to me makes no sense:
Code: Select all
    FROM
      view1 o,
      view2 a
    WHERE
      o.period = YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm,
                                                                                  GETDATE() - (DATEPART(DD, GETDATE())
                                                                                               + 1))
      AND o.period = a.period


  UPDATE
    table1
  SET
    average_balance = (
                       SELECT
                        SUM(Final_Balance) / DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))
                       FROM
                        table2 m2
                       WHERE
                        period BETWEEN YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + 1
                               AND     YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))

Both of these are from the same procedure and are laid out at the same time. In the fiste statement the o.period = YEAR(... is wrapped two times and indented like crazy but the last where-statement in the sub-query isn't wrapped at all. I'd also think that the indentation setting would place the last sub-select one tab (2 spaces in my case) from "average_balance" while it is actually aligned with the opening parenthesis. The indentation in the subselect is also only one character...
Lumbago
 
Posts: 25
Joined: Mon May 22, 2006 9:03 am

PreviousNext

Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests