Commenting Stored Procedure

MAK made this comment in another thread. I thought it was worthy of its own thread.

Quote:

---

Originally Posted by MAK

Required data dictionary and more documentation on the procedures.

---

For a standard CRUD procedure, I generally have few comments or no comments at all. If you have 'CREATE PROCEDURE' code at the beginning and error checking/reporting code at the end wrapped around a simple SQL statement, what comments are required?

Certainly, if the procedure is long and complicated, it should have some comments. (But why is it long and complicated?)

What I don't think is necessary are comments on things like input parameter names and types. Just look at the code. Result columns from a SELECT statement? Again, just look at the code.

If there is something that is not obvious, it definitely should be commented. If you find that you have to comment your SQL code, ask yourself if there is a way to rewrite the non-obvious parts so that they don't require comments.

If you are writing 30 lines of comments for a 1000-line stored procedure, you may have a coding issue, not a need for documentation.

The requirement is different for SQL scripts. Scripts often perform multiple actions that have to be done in sequence. Commenting is very important here.

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.