[From SQL Server Magazine
To debug a T-SQL stored procedure in SQL Server 2000, you use the Query Analyzer. You simply right-click the stored procedure and choose Debug. In SQL Server 2005, SQL Server Management Studio replaces the Query Analyzer. It's logical to assume that to debug a T-SQL stored procedure in SQL Server 2005, you simply right-click a stored procedure and select Management Studio's debug feature. Unfortunately, it's not that straightforward. A debug option is nowhere to be found.
The article continues at