I don’t think I’ve ever heard of anything that allows you to debug SQL-stored procedures, for example, you can debug the "real" application code. (This applies to 7.0, 2000 and 2005, but the jury still in 2008).
When I have to do serious debugging on unfamiliar stored procedures (which may be mine a few months after I wrote them), I cut "paste the code into the SSMS request window, replace the parameters with the appropriate DECLARE and SET, comment out the problematic statements (RETURN ) and run it. In most cases, temporary tables (# temp tables, not @table variables) will still be present after it starts. Finding and replacing # with ## makes them global and accessible from other query windows, which can help. Commenting Block Saving code and running partitioned sections can be very useful.
Some other tricks I did for serious ugly procedures (hundreds or even thousands of lines of code):
Add the @Debug parameter, the default is 0. In the appropriate sections of the procedure, add the "IF @Debug = 1" blocks in which you can print the current values of the variables and / or the contents of the temp table. A useful form may be:
SELECT 'DebugBlock3' DataSet, * from
Based on this, another trick is to define #Temp tables as follows:
IF @Debug = 0 or object_id('tempdb.dbo.#MyTempTable') is null CREATE TABLE
In this case, if you first created the #Temp table in the query window, and then from the same window call the procedure with @Debug = 1, as soon as the procedure is completed, the temp table will still be there, any final contents were filled.
When working with dynamic code (shudder), I always get @Debug, which works with the values 0, 1 and 2, with a comment
-- Debug control: 0=do the work, 2=List out dynamic code, 1=Both
And subsequent code blocks, such as:
IF @Debug > 0 BEGIN PRINT 'Comment about the following chunk of text' PRINT '-----------------------------------------------------------' PRINT @Command END IF @Debug < 2 EXECUTE (@Command)
Yes, this is pain, and it is not particularly convenient, but this is what I have come across over time. I honestly don’t think that you can have serious debugging in SQL, like testing the current state, checking the contents of the table and, as a rule, gouging, and step-by-step code can lead to locking, deadlocks, or wildly inconsistent results if either still using the database at the same time - and I would not even like to have the remote possibility that this ever happened in any Production for which I was responsible.