I was looking at a stored procedure the other day, trying to troubleshoot an issue. The stored procedure was well structured, short and relatively simple. The problem was, I didn't know what it was trying to accomplish. There were no code comments.
I've never been one for over commenting code. While I often appreciate it, I'm not convinced it has a positive ROI for every query.
For example, if I have a stored procedure named GetEmployeeAddress, it shouldn't require too many comments. If you over-comment in a stored procedure like this, I think you get negative ROI.
SELECT City, State
FROM Employee
WHERE EmployeeId = @employeeId
But if there's some unexpected logic or side effect, a comment is probably required. For example, if the GetEmployeeAddress stored procedure joins with the LunchMenu table, you probably need to explain why. There's likely a good reason, e.g. the intent of the function has changed, but it was too expensive to update all the downstream customers. But even if there's a good reason, it must be documented so someone fixing a bug knows why the unexpected code exists.
SELECT City, State
FROM Employee
INNER JOIN LunchMenu ON Employee.Allergies = LunchMenu.Allergies <-- probably requires a code comment
WHERE EmployeeId = @employeeId