He had no idea there was a debugger in Query Analyzer because he rarely opens the Object Browser. Even when he opens the Object Browser, it's basically to drag column names into the query window for use in various queries.
Probably my colleague is not alone in this regard, so here is a brief guide to running and using the debugger in Query Analyzer. Follow these four steps:
1. Open Query Analyzer and connect to a database of interest.
2. Open the Object Browser by pressing [F8] or going to Tools | Object Browser on the Query Analyzer menu.
3. Select the stored procedure of interest.
4. Right-click on the stored procedure's name and select Debug from the pop-up menu.
You should see an image like this:
Your debugging process begins here. Each parameter is listed, along with its data type and direction (in or out). There are a couple of details I want to point out in the illustration:
First, you can either supply a value for any given parameter, or set its value to NULL. This is especially useful when you write procedures whose parameters may be either.
Second, the Auto roll back checkbox enables you to make experimental changes in the stored procedure without affecting the database itself. In other words, you have a sandbox in which to play around—until you are satisfied that your changes will correct the problem you are trying to fix.
In the next figure, I loaded a different stored procedure, one with an output parameter; you might recognize this code from my article on recursion in T-SQL. It's the factorial algorithm, using BigInt declarations:
Note the position of the mouse pointer in Figure B. Directly above it, you see a small right arrow in the margin, indicating the current execution point. From here, you can do pretty much anything you could in a classic debugger from any programming language.
The debugger's toolbar contains buttons for all the typical actions, and with standard shortcut keys such as [F5] = Go, [F9] = Set Breakpoint, and so on. Move the mouse across the toolbar slowly and read the control tips. If you have any experience using another debugger such as the ones in Visual Basic, Access, or Visual C++, you'll be right at home in no time.
If you, like my colleague, didn't know about the debugger built into Query Analyzer, I expect that your appreciation of this tool will increase significantly. Of course, we all write perfect code almost every time out, but it's comforting to know that on those rare occasions when we make a mistake, help is just a few clicks away!