How can we help you today? How can we help you today?

Refactoring other people's code

I have been using Refactor quite a bit lately, usually to make code written by others before me conform to my standard layout. One feature I would like to see is to help standardize SQL, although it is not a big deal if it does not make it into the final version. I have found Refactor is fantastic for taking a 2000 like TSQL script with like 50 different SELECT, INSERT and UPDATE type statements and laying the whole thing out in a readable consistent fashion in a matter of seconds. But I would like to change the SQL if possible to make it easier to read as well. Such as:

select c.fname First_Name, c.lname Last_Name, co.discount
from customers c
join orders o on c.custid = o.custid
left join coupons co on c.custid = co.custid


One of the first things I would do to this code is change the aliases and join syntax. Even though the code is legal, it not easy to comprehend for everyone.

select c.fname AS First_Name, c.lname AS Last_Name, co.discount
from customers AS c
INNER join orders AS o on c.custid = o.custid
left OUTER join coupons AS co on co.custid = c.custid
schaitel
0

Comments

2 comments

Sort by
  • drsql
    I totally agree about the usefulness. Web code, coworkers code that is rarely indented (I like private forums), etc. My favorite use so far has been to take some dynamic sql I had embedded in my code and straighten it out using refactor.

    I like adding AS for sure, and certainly OUTER (I wouldn't quibble with INNER, mind you). Same pass I would like to apply dbo. to the table names (or whatever their actual schema is) and change the aliases to be the full name of the table (without dbo.))

    I know it is personal preference, but the main reason for posting here tonight is to say: Great Job, I will probably use the refactor thing more than any of the other gadgets I am beta testing with you right now. It is super handy.
    drsql
    0
  • Andras B
    schaitel wrote:
    I have been using Refactor quite a bit lately, usually to make code written by others before me conform to my standard layout. One feature I would like to see is to help standardize SQL, although it is not a big deal if it does not make it into the final version. I have found Refactor is fantastic for taking a 2000 like TSQL script with like 50 different SELECT, INSERT and UPDATE type statements and laying the whole thing out in a readable consistent fashion in a matter of seconds. But I would like to change the SQL if possible to make it easier to read as well. Such as:

    select c.fname First_Name, c.lname Last_Name, co.discount
    from customers c
    join orders o on c.custid = o.custid
    left join coupons co on c.custid = co.custid


    One of the first things I would do to this code is change the aliases and join syntax. Even though the code is legal, it not easy to comprehend for everyone.

    select c.fname AS First_Name, c.lname AS Last_Name, co.discount
    from customers AS c
    INNER join orders AS o on c.custid = o.custid
    left OUTER join coupons AS co on co.custid = c.custid

    This is a very good suggestion. There are quite a few "fixes" that could be applied to code, including changing 6.5 syntax to JOINS, the ones above, getting rid of select * automatically, changing hints to 2005 style. Due to time restrictions we will not include the above in the first version, but I saved this post for the subsequent version of the tool. (expanding select *s will be in the next beta though). Many thanks for your feedback,

    Andras
    Andras B
    0

Add comment

Please sign in to leave a comment.