What are good methodologies for creating sprocs that reduce the pain of debugging? And what tools are out there for debugging stored procedures?
Perhaps most importantly, what are indications to look out for that errors are happening in a sproc and not in the code? I hope I'm not all over the board too terribly bad here. Votes for answers to any of the above. Thanks.
For what it's worth, I work in a .NET environment, SQL servers.
Here's some advice that was reiterated to me today - if you're adding a join to an important query on the production database, make sure it's safe when there is a null field in the joining table.
LEFT JOIN
I broke an important page for 20 minutes before we figured out that it was my small, rushed stored procedure change.
And make sure you test your procedures when you make a change. To do this, I like to put a simple test query in the comments of the procedure. Obvisouly, I failed to do this today :-(
/************************************
MyProcName
Test:
-----
exec MyProcName @myParam
*************************************/