How to lay out SQL Code

Question

How to lay out SQL Code

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

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

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)

CAGreensfelder SSC Eights! Points: 919 More actions · Answer 2

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.)

dean gross SSCrazy Points: 2632 More actions · Answer 3

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

SqlNightOwl SSCrazy Points: 2333 More actions · Answer 5

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 6

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

SqlNightOwl SSCrazy Points: 2333 More actions · Answer 7

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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'

😎

SqlNightOwl SSCrazy Points: 2333 More actions · Answer 9

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

Pete Dobbs-356510 Valued Member Points: 73 More actions · Answer 10

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).

CAGreensfelder SSC Eights! Points: 919 More actions · Answer 11

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.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 13

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)

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 14

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.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 15

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)