How to lay out SQL Code

Question

How to lay out SQL Code

Viewing 15 posts - 31 through 45 (of 90 total)

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

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

Thanks for the feedback, Tom... the standards are kinda long... most of them are really based on common sense and things that have actually gone wrong at my current company. What I've found is that common sense isn't so common... a lot of maliciously compliant developers adopted the wonderful idea that if it wasn't in the standards, they wouldn't take a gentle nudge from me... so I put every bloody thing in the standards and simply doing code reviews by the numbers saying something like "the following code falied the following standards" and then list the standards by number only... forced them to lookup, read, and understand the standards that were frequently violated. Like I said, quality of code has gone way up and the number of errors found in QA (quality assurance) has gone way, way down.

The good part about all of that was... I had full management buy in. It took me over a year to get them to buy into it, but it was worth it.

--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: 736255 More actions · Answer 2

Management buy-in is key, otherwise you'll have trouble enforcing any standards.

SqlNightOwl SSCrazy Points: 2333 More actions · Answer 3

Jeff is the hero of the hour -- and maybe even longer!! I've been intending to add a document on code style to compliment the one on naming and your document got me a long way down that road. I may not use it 100% as it but your thought process is clear, concise and well laid out.

Some folks appear to be hung up on not being able to apply their own style thinking it limits their creativity. BUNK! The person that pays you is who you write the code for and your code should follow their standard. I agree with Jeff that the standard should be published, well understood and widely available (maybe an online help file). Write up the standard, have the developers sign an agreement that they've read it, understood it and will follow it. I've gotten to old and cranky to want to deal with developers delivering half baked code that they can't explain an hour after they wrote it.

--Paul Hunter

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

I have a really unique method of getting developers to follow the standards... I take them out to dinner and feed them nice juicy pork chops... with a slingshot! Heh... only takes 1 and they suddenly get all compliant and all... 😉

Anyway, could you post your document on naming conventions... I've also been meaning to write one and maybe a shove in the right direction would move me off top dead center on the task... thanks.

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

Manie Verster SSCertifiable Points: 7152 More actions · Answer 5

Matt, congrats with the gift cert. May ther be many more!;);)

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 6

Hi Tony,

When it comes to naming conventions and writing standards I can be quite a perfectionist. When I cames to tables I will use PascalCase names like CustomerName, CustomerId and yes, like Tom to use the same names in foreign keys like CustomerMaster.CustomerId with CustomerAddress.CustomerId. I do not have a problem with underscores but I just learned this way so that's how I do it.

When it comes to writing stored procedures I like, especially with large procedure where you insert or update 5 or 6 tables, to have indentation. Make the query readable when someone else has to work with it. There is nothing worse when I have to work on someone else's queries and you do not know which begin fits with which end. Eventually the client thinks you do not know your job because you have to spend hours making the procedure look like something you can understand. I like to use all lower case and definitely use indentations. You have to make sure that you can easily track all the begins with the ends.

Where it comes to names for stored procedures I do it all in lowercase and I name them as follows:

select procedures sp_list or sp_get or sp_find

sp_list for normal list procedures like lists for reports and/or lists on screens

sp_get for listing one tables data to get in a vb function etc.

sp_find for dropdown lists mostly

insert_tablename, update_tablename, delete_ tablename

I think these speak for themselves. Yes, I use underscores with stored procedure names.

I think the secret is that if you drop dead today, someone can take over from you with ease.

I think it is good to have standards but let each person or group of persons make that for themselves.

Manie

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Cliff O'Reilly Grasshopper Points: 13 More actions · Answer 7

Manie,

are you saying that you name stored procedures "sp_ ..."? I thought that was bad practice due to the way SQL Server will check in the Master database for procedures that start that way?

Personally I use "usp_ ...".

Cliff.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 8

Hi Steve,

If I could just quickly comment on your examples of code writing. I would do it as follows

SELECT a.bankid, a.datum, a.instruction, a.amount, a.description,

a.detail from bs787 a

left join 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'

I would think this readable. When I get to a procedure and it looks like this, I know exactly what to do.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 9

Cliff,

Once again I have to say that I inherited this database. I don't know if I would have done differently if I had done so myself. After all, the Master database if vastly different from my database. How could there be a problem?! Some comments on this please???:hehe:

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

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

I'm not certain of the validity of this, but from what I've heard: when a call is made to a stored procedure named with the prefix 'sp_', Sql will look in the Master database for a match first - before it looks in the database where the call was made.

I heard this a long time ago and immediately stopped prepending my procedures.

In fact, these days I don't use any prefix in my databases - I'm not sure what purpose there would be in using these. Tables are listed beneath the table node in the database tree, views beneath the vews node, stored procedures beneath the stored procedure node, and so on. I generally name my sp's with a verb which makes sense for the function, such as Get... Save... Update... Delete... and so on, followed by what the verb is acting on eg. GetSalesFiguresForUser or UpdateAddress.

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

Mannie,

If you inherited the db, it is what it is and you're stuck with it.

But if I were writing that query, I'd write:

SELECT a.bankid

,a.datum

,a.instruction

,a.amount

,a.description

,a.detail

FROM bs787 a left join 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'

I can find the WHERE clause at a glance and quickly sort through all the join conditions because there is only one per line - I don't have to carefully read the entire line to parse out each different condition. (I keep waffling back and forth on the "comma first" thing. Sister Mary Whatever's influence is still going strong after 40+ years!!)

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

Curses - web site ate the spacing in previous post.

David Jackson SSCertifiable Points: 6414 More actions · Answer 13

wrap it in a code tag.

The editor may eat this but I'll try

Select col from someTable

[/code]

It'll keep the spaces, but it does bugger up people, at least me, from copying it and pasting it into QA. I lose the line breaks! 🙁

Am I the only one this happens to?

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

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

No, you are NOT the only one. I thought _I_ was doing something wrong. I copy Jeff's beautiful code and have to run through reinserting the line breaks. Sigh.

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

No, no! Copy the code and paste it into MS Word... do a Search and Replace...

Search for ^l (circumflex small-"L") and replace it with ^p (circumflex small-"P"). Then copy from there and paste into QA... formatting will be preserved.

Steve Jones and Tony Davis are working on the fix but they're run into a couple of problems along the way. I don't know when they will impliment a fix but the WORD trick works for now...

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