My comments vary. Usually we start off with the following:

/****************************

InstallDB: MyDB1

CreatedBy: Brandie

CreatedOn: 2012.09.26

Description: This proc pulls the orders made within the last week that were not

serviced within 24 hours.

Revision History

2013.09.26 / Brandie / Request 184

Changed date range on proc to include orders that were serviced within

5 minutes due to issues with closed orders that were never sent.

******************************/

And then inside the stored procedure, I make notes where WHERE conditions got changed or new columns were added or tables added / removed to JOINs. Mostly this is because we have a BU that changes their minds on what business rules they are using and why. These comments have saved us on numerous occasions where I've had to back out a single request that comes up every 6 months that the BU forgot was backed out because what they keep asking for doesn't work.

Also, the more complicated my queries get (numerous JOINs and subqueries), the more likely I am to stick a comment that says something like "Grab the quantity only when status is X, Y or D". Yes, I am explaining my code, but sometimes it does really get that convoluted and it makes it easier not only for other people to read it, but for me to remember why I wrote a particular CASE statement or a specific formula.