In SQL 2000 you could debug stored procedures through Query Analyser. Today I needed to work out how to do it in SQL 2005. The only way I found is via Visual Studio 2005. Register the database in Server Explorer, then within Server Explorer navigate to the stored procedure to debug, right click on it and select "Step into stored procedure". If parameters are required then you'll be displayed a dialog allowing you to put in appropriate values. From here on it is just the same as debugging normal code using VS 2005 - F10 to step over, F11 to step into etc.

Happy debugging!