Simon Frazer shares some tips:
At some point, every SQL developer or DBA will need to debug T-SQL scripts, either to verify that they behave as expected or to track down the root cause of a problem. Whether you’re building something new or investigating a production issue, debugging is an essential part of the process.
There are several techniques available for troubleshooting, and it’s important to approach this differently depending on whether you’re working in a production or non-production environment. Each environment has its own risks and constraints.
Click through for Simon’s process. I also echo Simon’s sentiments at the end regarding the SSMS debugger—I know people who are passionate about it and mourn its passing, but I was never one of those people. It was far too easy to get in trouble with it, especially in shared environments.