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.

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

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:

Debugging in SQL 2008 R2 Debugging in SQL 2012
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:

The currently executing line

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:

Stepping into a procedure

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:

Step into query

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:

Calling procedure

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:

Run to cursor

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:

Stop debugging tool

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.

This blog has 0 threads Add post