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 two 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 (this blog) Using breakpoints 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 two 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 (this blog)
Using breakpoints
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

Basic debugging of SQL

Assuming that you've read the first part of this blog, you're now ready to debug your query!

Starting the Debugger

There are lots of ways to start debugging! Assuming that you have a query window open, you can use the Management Studio toolbar:


SQL Server 2008 R2	SQL Server 2012

Alternatively, you can press Alt + F5 in either application. Whichever method you choose, SSMS will mark with a yellow arrow the line of SQL that it is about to execute:

SQL is about to execute the statement marked with an arrow.

Stepping through your SQL

To execute a line of SQL you have two choices - you can step into a procedure, or step over it:

The menu shown is for SQL Server 2008 R2 - the 2012 one is almost identical.

As the menu above shows, you can press F11 to step into a procedure, or F10 to step over it. For most lines of code, there won't be any difference:

It won't matter whether you step into this statement or step over it.

However, when you're calling a stored procedure or user-defined function, there is a big difference:

It makes a big difference whether you step into or over this call.

Here's what will happen for the above line:

Option chosen	What will happen
Step over	SQL will run the stored procedure in its entirety, and leave the yellow arrow pointing to the next line of code in the main query.
Step into	SQL will open the stored procedure in a new window, and leave the yellow arrow pointing to the first line of code in it.

The moral? If you're happy that your stored procedure isn't the source of your problems, step over it; otherwise, step into it. If you change your mind, you can always press SHIFT + F11 to finish a stored procedure and return control to the calling query!

Changing the next statement executed

You can choose to run a query up to the current cursor position:

Right-click on a statement while debugging to execute a query up to (but not including) the statement.

Normally you can click and drag on the yellow arrow to change the next statement to execute, but in Management Studio this doesn't seem to work. Perhaps just as well - as the tooltip says (with terrifying understatement) that "this can have unintended consequences".

Stopping Debugging

When your query finishes, so will your debugging. If you want to end debugging early, however, you can click on the tool below to stop debugging:

Click on this tool to cancel debugging.

You can also press SHIFT + F5 to do the same thing.

Now we've looked at basic debugging, it's time to look at some of the tools you can use, starting with setting breakpoints.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

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