You may start with writing some 'useful comments' in your stored procedure source. There are always some sequential instructions in any kind of code. In my experiences, if you want to understand some code, comment is the most powerful tool to use.
Here are my steps to understand the 'flow' of code:
- Writing the 'purpose' of the stored procedure on the top of source.
- Writing the 'main actions' to archive the purpose you wrote; you may 'split' the code by comments.
- If you need more detailed action in main action, split it in nested code.
Examples:
/**
* 1. get the data of XXXX
* 2. update the value of XXXXX
* 3. ......
*/
/**
* declare variables
*/
DECLARE ....
-- :~)
/**
* get the value of ..... (Main action 1)
*/
SELECT @foo = f_aa
FROM ss_foo
-- :~)
/**
* if something is in some status, do another thing ....(Main action 2)
* or calculate the value from .....
*/
IF ....
BEGIN
/**
* get the data of XXXX (Sub-action 2-1)
*/
SELECT @a = b_aa
FROM ss_bar
WHERE b_cc = @foo
-- :~)
/**
* calculate the XXXXX of another thing (Sub-action 2-2)
*/
let @another = sp_another_func(@a)
-- :~)
END
-- :~)
I think the key of writing 'useful comments' is trying to write precise context of 'business rules in computing', not writing the implementation of database components. In other words, don't write too many details in your code.
The document of bussiness rules in computing, as presented, would help you to write other documentations, and ease the maintenance of documents.