This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Debugging SQL stored procedures and queries Basic debugging of SQL Using breakpoints (this blog) 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.

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.

Debugging SQL stored procedures and queries
Basic debugging of SQL
Using breakpoints (this blog)
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

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:

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:

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:


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:


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:

Choose this menu option to display the breakpoints window.

The window displays more information than you could ever want!

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:

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.

Parts of this blog
Debugging SQL stored procedures and queries Basic debugging of SQL Using breakpoints (this blog) Other debugging tools

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using breakpoints

Setting and Removing Breakpoints

Disabling breakpoints

Listing and managing breakpoints using the breakpoints window

Removing all of your breakpoints

Head office

London

Manchester