We will debug our procedure, not from Management Studio but from the Visual Studio 2005 (only Team Systems or Professional editions) development environment. I mention this because under SQL Server 2000 we are able to debug stored procedures using Query Analyzer. Perhaps debugging capabilities will be added to Management Studio in the future.
Before any debugging can take place, you first must create the store procedure. This can be done either by using Management Studio/New Query or by building the procedure graphically in Visual Studio 2005. Stored procedures can be created in Visual Studio by opening a new database project and using the Visual Studio Installed Templates.
Either way, once the procedure exists, you are ready to start debugging from Visual Studio 2005. When you start up Visual Studio for debugging purposes, you don't have to create a project. Instead, you can create a connection to your database from Server Explorer (You will have to provide your server name along with your login credentials and choose the AdventureWorks database.) Then you can drill down and open up the stored procedures folder. Next, right click on the procedure you wish to debug and choose Step Into Stored Procedure from the context menu. Then you are ready to start debugging!
Note: Before you can start "Remote debugging", you have to install "msvsmon.exe" from the visual studio CD or the sqlserver directory "C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe".
Micorosft development team is looking to implement a better way within SQL Server 2008 and as of now 2005 is only way out with Visual Studio.