Real-live debugging tools first made an appearance in SQL Server in SQL Server 2000. Much as with SQL Server 2005, you needed your settings to be just perfect and several starts to align to get it working. After that, it was wonderful.
The debugging effort for SQL Server 2005 is highly integrated with Visual Studio and does work fairly well.
I'm not going to kid you: The debugging tools are still a pain at best (and impossible at worst) to get functional. Given the focus on security in recent years, so many parts of your server are locked down to external calls now that remote debugging (which is what you're going to want if you have more than one developer on the project) is particularly difficult to get going. All I can say is, hang with it and keep trying. It's worth the effort when you get it working.
11.12.1. Setting Up SQL Server for Debugging
SQL Server no longer ships with a debugger as part of the product — you must have Visual Studio in order to debug even your T-SQL-based stored procedures. As of this writing, the Express edition of Visual Studio is free for one year.
Depending on the nature of your installation, you may have to do absolutely nothing to get debugging working. If, however, you took the default path and installed your SQL Server to run using the LocalSystem account, debugging might not work at all. The upshot of this is that, if you want to use debugging, you need to configure the SQL Server service to run using an actual user ...