How to lay out SQL Code

  • Almost forgot... if you copy from 1 line ABOVE the code window to 1 line BELOW the code window, leading spaces will also be preseved.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you! I'm a wiz at MSWord but never thought to use it.

    (Used to teach "Introduction to Microcomputer Applications" at the local college - when I started it was DOS, WordPerfect, Lotus 123, dBaseII.. I called it driver's ed for the PC. Now they teach it in high school with MS Office.)

  • Andrew Novick's Transact SQL User Defined Function's book has an excellent discussion on Documenation, Formmatting and Naming Conventions in Chapter 6. In this, some of his reasons for the comma, or separator first formatting approach are:

    makes it easier to add comments to the end of the line

    makes it easy to comment out a line

    give visual prominence to the separators

    Dean

  • CAGreensfelder (3/4/2008)


    Thank you! I'm a wiz at MSWord but never thought to use it.

    (Used to teach "Introduction to Microcomputer Applications" at the local college - when I started it was DOS, WordPerfect, Lotus 123, dBaseII.. I called it driver's ed for the PC. Now they teach it in high school with MS Office.)

    I may need to go back to high school then. I recently installed MSOffice 2007 on one of my machines - and the UI is a disaster IMO. I'm still trying to find how to get my old-style menus back (it seems to hide EVERYTHING, including preferences, etc....). It's making me feel like a moron all over again (not so hard a task to do, according to my wife:))

    Again - I'm wondering who thought this new look was in any way an improvement....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I just don't get the verb-object (VO) naming style as opposed to the object-verb (OV) style. Using VO you get verbs are a prefix (which stinks).

    Let's say you have a moderate size database with 100 tables. Not large by any standards. Now assume you wrap each table in a set of four methods; delete, get, insert and update. In this scenario there are 99 objects between the first delete[SomeObject] and get[SomeObject] another 99 to get to the insert[SomeObject] and yet another 99 to get to update[SomeObject]. Now the customer/company comes to you and says they need to add QQQQ to all the SomeObject related procedures as part of the primary key. Whew, you just took on a whole lot of scrolling just to update those four objects. However, if you turn VO into an OV style the four procedures are listed one after the other so, it's click and edit, bing, bang, boom and you're done. Plus, you were able to handled that odd ball [SomeObject]search procedure that everyone forgets about. Now you're the hero and can easily justify these high salaries. 😉

    --Paul Hunter

  • dean gross (3/4/2008)


    Andrew Novick's Transact SQL User Defined Function's book has an excellent discussion on Documenation, Formmatting and Naming Conventions in Chapter 6. In this, some of his reasons for the comma, or separator first formatting approach are:

    makes it easier to add comments to the end of the line

    makes it easy to comment out a line

    give visual prominence to the separators

    Dean

    Dean,

    Do you actually believe it?

    makes it easier to add comments to the end of the line

    Let's see. Go to the end of the line; space; dash dash; space ; comment. It appears to be about the same.

    makes it easy to comment out a line

    Same thing. Either way you have to consider that one of the lines is not going to have a comma.

    give visual prominence to the separators

    One line per column is pretty prominent.

    I haevn't heard an argument yet that would convince me to give up the much more aesthetically pleasing comma last scripting.:cool:

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Matt Miller (3/4/2008)


    I may need to go back to high school then. I recently installed MSOffice 2007 on one of my machines - and the UI is a disaster IMO. I'm still trying to find how to get my old-style menus back (it seems to hide EVERYTHING, including preferences, etc....).

    Yep. I'd say MS pretty much messed up a good thing. Word 2003 = good; Word 2003 + 4 = bad.

    --Paul Hunter

  • Everyone has their own way of laying out code. This is how I like to do it using the example several have used in this thread:

    SELECT

    a.bankid,

    a.datum,

    a.instruction,

    a.amount,

    a.description,

    a.detail

    FROM

    dbo.bs787 a

    left join dbo.bankstatements b

    ON (b.bankid = a.bankid

    AND b.datum = a.datum

    AND b.instruction = a.instruction

    AND b.description = a.description

    AND b.detail = a.detail

    AND b.amount = a.amount)

    WHERE

    isnull(b.bankid,0) = 0

    AND a.[instruction] <> 'Open'

    AND a.[instruction] <> 'Close'

    😎

  • Tom Garth


    I haevn't heard an argument yet that would convince me to give up the much more aesthetically pleasing comma last scripting.

    I had that same attitude until I was forced to work with it for 3 months. Now I've got the religion -- can I get an amen?

    select col1

    , col2

    -- , col3

    from sometable

    -- versus...

    select col1,

    col2 /*,

    col3 */

    from sometable

    The only place I've found even a small issue is if it's the first column. Then the first style is easier. I rarely have only one column so, all things being equal, I'll live with the better approach of the second style and just deal with the "first column pain".

    --Paul Hunter

  • paulhunter, I can see your point about OV instead of VO and if the consensus is that most people prefer OV then that's fine. Personally I've never considered it difficult to locate a given stored procedure and I've worked on some pretty large databases.

    I think the VO method probably got ingrained in me from other languages - calls to methods such as: getString, writeLine, goSub and so on. My mind has just always put things that way round.

    I might consider changing my habit if it's considered enough of an issue for people (I like being considerate to the next guy/girl along).

  • SSCrazy - (totally off topic but...) I agree wholeheartedly about Office 2007. I can't find anything and the help doesn't even let you look up the old menu sequence to find where they hid the feature you need. Maybe it's wonderful if you are starting from scratch but right now it's making me crazy.

    PaulHunter - I'm with you on the VerbObject but our shop standard is ObjectVerb so that's that.

    The filter option on SSManagement Studio helps tremendously. I right click on "Stored Procedures" and filter on "Patient" and DeletePatient, GetPatientGenInfo, GetPatientContact, GetPatientDiag, ListPatient, SelectPatient, UpdatePatient... are all there together.

  • CAGreensfelder (3/4/2008)


    SSCrazy - (totally off topic but...) I agree wholeheartedly about Office 2007. I can't find anything and the help doesn't even let you look up the old menu sequence to find where they hid the feature you need. Maybe it's wonderful if you are starting from scratch but right now it's making me crazy.

    You will adjust to it, and it won't be so bad. The hot keys CTRL-O and CTRL-N still work. Tap the alternate key to see the old menu items. Click the big fat Office icon in the upper left corner to see the new menu layout, and at the bottom of that, click the Word Options button to modify the quick Access bar next to the Office icon, and to access other settings including the default document format.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • dean gross (3/4/2008)


    Andrew Novick's Transact SQL User Defined Function's book has an excellent discussion on Documenation, Formmatting and Naming Conventions in Chapter 6. In this, some of his reasons for the comma, or separator first formatting approach are:

    makes it easier to add comments to the end of the line

    makes it easy to comment out a line

    give visual prominence to the separators

    Dean

    It makes the first line hard to comment out just like the other method makes the last line hard to comment out.

    SQL is supposed to be an "english like language"... in English, we don't start new lines with a comma. I guess I can understand why some people might like it... I just don't allow it in my shop because more people seem comfortable with the commas at the end of the line...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I agree that commas at the beginning aren't natural. It took me awhile to get used to it, but after years of comma ending, I found comma beginning much easier to work with when I need to test something, troubleshoot, etc.

  • I found comma beginning much easier to work with when I need to test something, troubleshoot, etc.

    How so? Commenting out lines using either style is exactly the same except for the first line in one style and the last line in another style. Other than that... zero difference. Adding comments to the end of a line in either style is also a no brainer. Other than the first or last line thingy, there's no real technical advantage to either style.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 46 through 60 (of 90 total)

You must be logged in to reply to this topic. Login to reply