Debugging stored procedures in SQL Server Management Studio

Question

Is there a way to step into the stored procedure code in SQL Server Management Studio? I know this is possible with Visual Studio, but I am looking for a dependable debugging solution from within Management Studio

Answer 1

Only with SQL 2008 and SSMS 2008. Select from menu 'Debug\Start Debugging' or press Alt+F5 and will launch the step by step T-SQL debugger.

On 2005 the only way is to attach Profiler and monitor for the SP:StmtCompleted event, which is not exactly debugger step-by-step, but at least you'll see the execution flow. Not to be done on a production machine, obviously.

Answer 2

I have written a pretty detailed blog post about it here:

http://www.diaryofaninja.com/blog/2010/11/23/debugging-sql-queries-function-amp-stored-procedures-with-sql-management-studio

Basically the gist of it is that you enter your sql query to execute your stored procedure, and instead of pressing F5 or hitting the exclamation, you hit the play button and use F10 and F11 to step through and step into your stored procs.

This is very handy but no one seems to use it.