562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
Assuming that you've read the first part of this blog, you're now ready to debug your query!
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.
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!
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.
|Parts of this blog|
25 Aytoun Street