How to debug T-SQL queries and stored procedures
Part four of a four-part series of blogs

This blog gives the low-down on how to start and use the debugger included in SQL Server Management Studio 2008 R2 and SSMS 2012.

  1. Debugging SQL stored procedures and queries
  2. Basic debugging of SQL
  3. Using breakpoints
  4. Other debugging tools (this blog)

If you'd like to learn more about writing SQL, you can either see our online tutorial (of which this is one small part) or attend one of our SQL courses.

Posted by Andy Brown on 06 June 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Other debugging tools

This page shows some of the other tools available in the Management Studio debugger.

The Locals window

The Locals window displays the value of any variable that you're currently using:

The Locals window

Here the integer variable @num currently holds the value 5.

 

You can display the Locals window using the following menu option:

Displaying locals window

Note that this menu is only available when you are in the middle of debugging a query.

Other debugging tools

If you're experienced at debugging, you may well have used other Visual Studio tools.  Confusingly, these can also appear in SQL Server Management  Studio even though they aren't actually useful.

This is because Management Studio is now written as part of Visual Studio, which has a far wider range of built-in debugging tools.

For those in the know, here are some of the debugging tools you probably won't want to bother using in Management Studio:

Tool Notes
Watch window Yes, yoiu can watch variables, but this doesn't offer any additional features over and above those provided by the Locals window.
Immediate and Command windows Although these can appear in Management Studio, they're not actually usable!
The call stack It's rare that you'll ever have the need to use this, since having stored procedures calling stored procedures calling stored procedures is not that common a scenario.!

And with that damp squib, I (and you) have reached the end of this blog on debugging T-SQL queries!

This blog has 0 threads Add post