I use an @debug or @test flag as the input variable for all complex stored procs.
What I do with it then depends on the proc. Normally, I issue a rollback if in test mode. I may also show the results of various inserts before the rollback to see if what I expected was what I got.
If I have dynamic SQL that when I use the @debug and in this case I print the sql instead of executing it, so I can check the SQL statement produced.
Of course I can have an @test (used only when I am affecting data and want to be sure I can roll it back) and an @debug (to print out dynamic SQl) in the same proc although I usually don't run in both modes at once.
I have also used an @print to print the steps as I go if I am debugging (helps alot to see where the thing failed!) or alternatively, you can use a table variable to hold the steps processed or any error codes and then select from the table variable after the rollback when in test mode to see what actually happened. You can even insert the values from the table variable into a permanent table if you need to see the data over time (we do this for complex imports so we can show the client how often their crappy data broke the import and caused us to have to do work to fix the issue.) It's important to use a table variable and not a temp table for this because it stays in scope after the rollback, a temp table rolls back.
(Note the table variable temp table stuff is SQL Server specific, I don't know if this will work with other databases.)
A profiler can also provvide the actual sql that was sent to the database and should be used especially if you are not using stored procs.