BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
|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".
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.