I don't think I've ever heard of anything that lets you debug SQL stored procedures like you can debug "real" application code. (This covers 7.0, 2000, and 2005, but the jury's still out on 2008).
When I have to do serious debugging on unfamiliar stored procedures, (which could be mine, months after I wrote them), I cut 'n paste the code to an SSMS query window, replace the parameters with appropriate DECLAREs and SETs, comment out problematic statements (RETURN), and run it. In most cases, the temp tables (# temp tables, not @ table variables) will still be there after it runs. Searching and replacing on # with ## makes them global and accesable from other query windows, which can help. Commenting out blocks of code and running the uncommented sections can be very useful.
Some other tricks I've done for seriously ugly procedures (hundreds, or even thousands of lines of code):
Add parameter @Debug, defaulting to 0. In pertinent sections of the procedure, add "IF @Debug = 1" blocks, within which you can print out current variable values and/or temp table contents. A useful form can be:
SELECT 'DebugBlock3' DataSet, * from #MyTempTable
Building on this, another trick is to define #Temp tables like so:
IF @Debug = 0 or object_id('tempdb.dbo.#MyTempTable') is null
CREATE TABLE #MyTempTable
(
ProductId int not null
,etc
)
With this, if you first CREATE the #Temp table in a query window, and then from the same window call the procedure with @Debug = 1, once the procedure is completed the temp table will still be there, populated with whatever the final contents were.
When working with dynamic code (shudder), I've invariable got @Debug set to work with values 0, 1, and 2, with the comment
-- Debug control: 0=do the work, 2=List out dynamic code, 1=Both
And subsequent code blocks like:
IF @Debug > 0
BEGIN
PRINT 'Comment about the following chunk of text'
PRINT '-----------------------------------------------------------'
PRINT @Command
END
IF @Debug < 2
EXECUTE (@Command)
Yes, this is a pain and not particularly convenient, but it's what I've come up with over time. I honestly don't think you can have serious debugging in SQL, as testing current state, checking table contents, and generally poking around while stepping through code could result in blocking, deadlocks, or wildly inconsistant results if anyone else is using the database at the same time -- and I'd hate to have even the remote possibility of this ever happening on any Production that I was responsible for.