Recently I came across a big stored procedure in our code. I needed to make some changes to it. That's when I decided this procedure could not go on with its size and complexity. It was time to refactor it.After I broke the procedure into many procedures and functions, I ran some regression tests. Initially I broke the functionality. I went back to good old debugging techniques. I wrote out a bunch of variables to a table. The values were always NULL.
Then it hit me. I had a bunch of OUT params that were not initialized. Therefore they were NULL by default. You can avoid this by always initializing OUT parameters. These were numeric variables. Incrementing NULL lefts the values NULL when I had the bug. Explicitly setting them to zero solved my problem.
