In my opinion.

a. There is a need for commenting inside the stored proc if we are not using RCS/VCS/PVCS/Clearcase type of version control. Usually Data dictionary will take care of all the functionality part. Any VCS will take care of version. But developers should make sure they add comments before checkin.

b. Simple procs that does insert/update/delete doesn't need commenting. The naming convention like p_ins_Mytable self explains.

c. Commenting required, when adding business logic into procedures. Commenting required, when writing smarter procs that could redirect the execution based on the parameter values and things like KPI. Stored proc written for reporting purpose needs commenting.