SQL Server 2005 how do I see what variables are being passed into a proc

Question

I'm trying to debug a stored procedure. I'm creating a logtable (just a regular table) and inserting the stored proc input values into it, however this is in the middle of transaction and my inserts are getting rolled back. Is there any way to commit my inserts that have them saved even when a rollback is issued.

Thanks

Answer 1

Check out the SQL Server Profiler to see what is being passed into your procs...

Answer 2

In code if you are calling procedures in a nested fashion only the outermost BEGIN TRAN ... ROLLBACK TRAN will have the ability to create records that will not be rolled back. However, SQL Server Profiler allows you to see exactly what Transact-SQL statements are submitted to the server and how the server accesses the database to return result sets.