How do you write effective stored procedure documentation?

Your code will last longer at your job than you will. And eventually you'll be taking over someone else's role/code. We all need to do our part to make sure that our code is well documented, not just to explain what the code is doing, but also WHY. Especially for odd and seemingly "bad" pieces of code, adding context can prevent problems when the next person doesn't know why you did that weird thing.

One thing I've found useful is there should documentation within the code, a searchable buzz word of the SPs function in sharepoint as well. Additonally we socialize the SP in a pseudo data dictionary schema. So when exposed it becomes SP as a service.

Context to the stored procedure can often be valuable. The original ticket/request or a quick blurb as to why you need to do what you're doing. Sometimes we need to do weird and "unnecessary" actions in our code. Providing an explanation for the next person can possibly prevent them from getting rid of something crucial while they're in the process of optimizing. Also, when making changes, consider adding dates to your change. Even if it's just adding/removing a filter. Testing now may prove successful, but results in the future may show that the filter you put in was problematic.

If any variables are used, they should have meaningful names that describe their purpose. For instance if you want to create a variable to store an customer's total purchases you should give the variable a name similar to @total_purchases rather than a cryptic name like @var1.

However you document, be consistent. Ideally in a format that's common with the rest of the team. This will contribute to the flow of the code and its readability.

If you do nothing else, add your name/ID and a date. Even if your change is super obvious/simple, document WHEN it happened. Even if you have version retention, it's much easier for the next person to find what you did. Especially if the next person needs to be make a similar change. They can just pop over to every spot you changed, add theirs, and be done that much quicker.

In my experience, documentation tools are inadequate for proper documentation, or aren't worth the effort. However, some may provide you with an ability to auto create documentation templates, allowing you to do high-level documentation faster. If you and your team have a consistent style of headers/documenting, consider something that you can prepare ahead of time.

Whenever you have a complex query that joins several tables, it is best to prefix every field in the SELECT clause with either the table name or the table alias assigned to that table defined in the FROM clause. This makes it easier for others to understand what is happening and not have to examine each table to find the fields.

When talking about MS SQL (and maybe others) this might seem obvious, when changing sprocs try to avoid using DROP and CREATE as this may lose some object settings and more importantly any permissions which has caused me pain in the past. In later versions of MS SQL try to use CREATE OR ALTER PROCEDURE instead. With some smart coding you can avoid suffering.

Few teams document well, primarily due to time restraints. ANY documentation is better than no documentation. Having a link to a document/ticket can allow you the space to set up good documentation later. Remember that documentation isn't just a header and a quick comment about you weird joining criteria. It's also letting someone know how to use your code, or how to troubleshoot problems by researching the data your code creates. What would you like to see if you were given your code for the first time?