Sql – What’s your favored method for debugging MS SQL stored procedures

debuggingsqlsql serverstored-procedures

Most of my SPs can simply be executed (and tested) with data entered manually. This works well and using simple PRINT statements allows me to "debug".

There are however cases where more than one stored procedure is involved and finding valid data to input is tedious. It's easier to just trigger things from within my web app.

I have a little experience with the profiler but I haven't found a way to explore what's going on line by line in my stored procedures.

What are your methods?

Thank you, as always.

Note: I'm assuming use of SQL Server 2005+

Best Answer

Profiler is very handy, just add SP:StmtStarting events, and filter the activity down to just your process by setting SPID=xxx. Once you have it set up, it's a breeze to see what's going on.