Visual Basic

Peering Inside Stored Procedures

One of the classic bugbears of client/server programming is that it's not possible to debug stored procedures interactively. Instead, you're forced into the traditional edit-compile-test cycle, treating the stored procedure that you're developing as an impenetrable black box. Pass in some inputs, watch the outputs, and try to guess what happened in between. Visual Basic 5 and Visual Basic 6 contain something that's rather useful: a Transact-SQL (T-SQL) interactive debugger.

There are a few constraints. First of all, you must be using the Enterprise Edition of Visual Basic 6. Also, the only supported server-side configuration is Microsoft SQL Server 6.5 or later. Finally, you also need to be running SQL Server Service Pack 3 or later. When installing Visual Basic 6, select Custom from the Setup dialog box, choose Enterprise Tools, and click Select All to ensure that all the necessary client-side components are installed. Once Service Pack 3 is installed, you can install and register the T-SQL Debugger interface and Remote Automation component on the server.

The T-SQL Debugger works through a UserConnection created with Microsoft UserConnection, which is available by selecting the Add Microsoft UserConnection option of the Project menu. Once you've created a UserConnection object, just create a Query object for the T-SQL query you want to debug. This query can be either a user-defined query that you build using something like Microsoft Query, or a stored procedure.

The T-SQL Debugger interface is similar to most language debuggers, allowing you to set breakpoints, change local variables or parameters, watch global variables, and step through the code. You can also view the contents of global temporary tables that your stored procedure creates and dump the resultset of the stored procedure to the output window. If your stored procedure creates multiple resultsets, right-click the mouse button over the output window and select More Results to view the next resultset.

Some final thoughts The combination of these two powerful interactive debuggers, including their new features, makes it even easier to step through every piece of code that you write, as soon as you write it. Such debugging usually doesn't take nearly as long as many developers assume and can be used to promote a much better understanding of the structure and stability of your programs.


How low can you get? One of Visual Basic 6's compile options allows you to add debugging data to your native-code EXE. This gives you the ability to use a symbolic debugger, such as the one that comes with Microsoft Visual C++, to debug and analyze your programs at the machine-code level. See Chapter 7 and Chapter 1 (both by Peter Morris) for more information about doing this.