How to debug stored procedures in sql server 2005

Question

How can I debug stored procedures in SQL server 2005.

Answer 1

It appears that in SQL Server 2005 you need to use the Visual Studio IDE - see this SO post.

This may have been changed in later service packs (because frankly, removing that from the management studio seems like a silly idea).

In SQL Server 2000 you could use Query Analyzer (right click procedure, debug)

In SQL Server 2008 debugging is back in SQL Server Management Studio (now an option in the toolbar).

Of course, the classic, tried and true way is to cut the contents of the procedure into a new query window, create the parameters you want, then use print and select statements to figure out what may be going wrong.

Answer 2

Use Visual Studio, by making a Data Connection to your SQL box, but don't do it on a production server, because it pauses execution using all kinds of scary low-level locking techniques. It will cripple SQL on whatever server you do it on, so do it on your local machine only if possible.