How to debug T-SQL queries and stored procedures
Part three 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 (this blog)
  4. Other debugging tools

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.

Using breakpoints

Breakpoints provide a great way to run a query normally until you reach a given statement, and then debug from this point onwards.

Setting and Removing Breakpoints

You can set any statement to be a breakpoint by clicking in the left (grey) margin:

Setting a breakpoint

Click in the grey margin to set a breakpoint for any statement (a red circle will appear).

 

Breakpoints for long SQL statements can be a bit misleading:

Multiline breakpoint

It looks like we've set a breakpoint for part of this statement, but in fact this isn't possible!

 

To remove a breakpoint, just click again (you can also press F9 to toggle breakpoints on and off).  When you run a query it will stop at any breakpoint:

Starting to debug Reaching a breakpoint
Continue debugging ... ... and execution stops here.

Note that breakpoints only work when you're already debugging a query (if you execute a query normally, breakpoints will be ignored).

Disabling breakpoints

If you disable a breakpoint, it makes it easier to reactivate it in the future:

Red circle right click Disabled breakpoint
Right-click on the red circle ... ... to disable a breakpoint.

A disabled breakpoint shows up as an empty circle: you can reinstate a disabled breakpoint by right-clicking again.

Listing and managing breakpoints using the breakpoints window

I've never used this window in my SQL career, but I'm feeling in a do-the-thing-properly mood.  You can display the breakpoints window as follows:

Displaying the breakpoints window

Choose this menu option to display the breakpoints window.

The window displays more information than you could ever want!

Breakpoints window

Here we have two breakpoints, one of which is currently disabled. For SQL the Condition and Hit Count columns will always contain the same values.  You can click on the Columns dropdown to fine-tune which columns of information are displayed.

Removing all of your breakpoints

Breakpoint build up, and it's a pain having to remove each one individually, so I often use this menu option:

Delete all breakpoints menu

Choose this menu option to remove all of your breakpoints in one go.

 

For the final part of this blog I'll take a look at some of the other debugging tools available for SQL Server Management Studio, including how to monitor the values of variables that you've created using the Locals window.

This blog has 0 threads Add post