Demystifying Debugging in SQL Server 2005

To debug a T-SQL stored procedure in SQL Server 2000, you use the Query Analyzer. You simply right-click the stored procedure and choose Debug. In SQL Server 2005, SQL Server Management Studio replaces the Query Analyzer. It's logical to assume that to debug a T-SQL stored procedure in SQL Server 2005, you simply right-click a stored procedure and select Management Studio's debug feature. Unfortunately, it's not that straightforward. A debug option is nowhere to be found.

To debug T-SQL stored procedures in SQL Server 2005, you use the Visual Studio 2005 development environment. However, before I show you how to use it, we first need a stored procedure to debug. Instead of using an existing stored procedure or creating one in Management Studio, let's use Visual Studio 2005's not-so-well-known project type named Database to create a T-SQL stored procedure. In Database projects, you can access the Query and View Designer, which provides graphical tools you can use to create T-SQL stored procedures.

How to Graphically Create T-SQL Stored Procedures

Database projects let you store database references, run SQL scripts and queries, and use command files to run multiple SQL scripts. You can also use the built-in support for source control of your script files.

You create a Database project by selecting New, Project from the File menu in Visual Studio 2005. Alternatively, you can use the keyboard shortcut of pressing Ctrl+Alt+N. In the New Project dialog box that appears, expand the Other Project Types node in the navigation tree in the left pane and highlight Database. Then, in the right pane, choose Database Project under the Visual Studio installed templates banner. Give the project a name and a location, then click OK.

You're now presented with a connection dialog box, in which you have a choice to reuse an existing connection (if one exists) or create a new one. Figure 1 shows the dialog box to create a new connection. The examples in this article use SQL Server 2005's AdventureWorks sample database.

After you've chosen the connection to use (or created a new connection), you've created a new Database project in a new solution. A solution is the largest unit of management in the Visual Studio 2005 development environment. It contains one or more projects with one or more code files.

In Visual Studio 2005, you have two Explorer windows. Solution Explorer, which appears on the right, lets you view projects and files inside a solution. Server Explorer, which appears on the left, shows information about database connections and system information related to servers on the network. If Solution Explorer and Server Explorer are hidden, you can make them visible from the View menu. Alternatively, you can use the keyboard shortcuts Ctrl+Alt+L (Solution Explorer) and Ctrl+Alt+S (Server Explorer) to make them visible.

To create a T-SQL stored procedure, select Add New Item on the Project menu (or press Ctrl+Shift+A). In the Add New Item dialog box, which Figure 2 shows, you can choose different item templates, including templates for stored procedures, trigger scripts, and table-creation scripts. You can customize the templates and even add your own templates.

To create a T-SQL stored procedure for debugging, choose the Stored Procedure Script template, give the script a name, and click OK. You're now presented with a template script file that contains some skeleton code to create a stored procedure. The template includes code to drop an existing stored procedure, create a stored procedure, and grant execute permissions. What is left for you to do is to write the code to be executed in the stored procedure. And you can even get help with this task. If you right-click the script file, you'll receive a context menu that includes the Insert SQL option. When you choose this option, the Query and View Designer (aka Query Builder) appears.

The Query Builder provides graphical tools for building not only stored procedures but also queries, views, and in-line functions. The Query Builder consists of four vertically stacked panes in the following order:

When you open the Query Builder, the Add Table dialog box, which Figure 3 shows, also appears. In this dialog box, you choose the tables, views, and functions you want to work with. As you can see, I've chosen four tables. Clicking the Add button adds the tables to the Diagram pane. As Figure 4 shows, each table gets its own box, which contains a check box for each column in that table. Eventually relationships between the tables appear in the Diagram pane. As Figure 4 shows, the relationships appear as lines between the tables.

In the SQL pane, the Query Builder creates a FROM clause based on the relationships between the tables. You can choose what columns you want by selecting the column check boxes in the various tables in the Diagram pane or by choosing the column names from the drop-down list in the Criteria pane. For example, in Figure 4, I'm selecting the ProductModel table's Name column from the drop-down list in the Criteria pane.

In the Criteria and SQL panes, selected columns with the same names are given aliases. By default, these aliases are Expr1, Expr2, and so on. In either the Criteria or SQL pane, you can give the aliases names that are more user-friendly.

In the SQL pane, you can change the SQL statement generated by the Query Builder if desired. When you're satisfied with the query, you can check the results by clicking the Query Builder's Execute Query button. If the results are acceptable, you click OK to insert the SQL statement into your script file. You can then continue to edit the code from within that file.

When you're happy with the stored procedure, it's time to create it in SQL Server 2005. If you've written applications in Visual Basic .NET or Visual C# .NET, you know that to create your application, you use the Build menu. In a Database project, there isn't a Build menu. Instead you save your script file, then choose Run from the Project menu. To see what has happened, you can open the Output window by selecting Other Windows, Output from the View menu (or press Ctrl+Alt+O).

If there aren't any errors in the Output window, the T-SQL stored procedure should have been successfully created. To double-check, open Server Explorer and expand Data Connections, the connection you're using, then the Stored Procedures node. The stored procedure you've created should be listed. If it's not listed, right-click the Stored Procedures node and choose Refresh.

How to Debug T-SQL Stored Procedures

As I just demonstrated, you can use Visual Studio 2005's Database project to create a T-SQL stored procedure. However, you don't have to debug that stored procedure from inside the Database project. You can debug it in Server Explorer.

Open Server Explorer and expand the Data Connections node. If there's a connection node for the database in which you want to debug the T-SQL stored procedure, expand that connection node as well. If there is no connection node, right-click the Database Connections node and choose Add Connection. Following the instructions I gave previously, use the dialog box in Figure 1 to add the connection.

After the connection node is place, expand it. Then expand the Stored Procedures node and select the T-SQL stored procedure you want to debug. To start the debugging process, you do something similar to what you did in SQL Server 2000's Query Analyzer: You right-click the stored procedure, then choose the Step Into Stored Procedure option. If your procedure takes parameters, the Run Stored Procedure dialog box will appear. In this dialog box, you set values for your stored procedure's parameters. When you have set the parameter values, you click OK to load your stored procedure code into the development environment. If your procedure doesn't take parameters, your stored procedure code loads directly into the development environment. As the debugging dialog box in Figure 5 shows, the execution stops at the first line of code in the stored procedure.

Near the bottom of the debugging dialog box, you'll find the Call Stack and Autos windows. The Call Stack window shows the code that's executing. The Autos window displays variables used in the current statement, the three statements preceding the current statement, and the three statements following the current statement. The Autos window is part of a group called the Variables windows. What these windows have in common is that they give you the ability to display, evaluate, and edit variables and expressions. Apart from the Autos windows, the other windows in the group are the Locals window, which displays variables local to the current context or scope, and the Watch window, which lets you add variables whose values you want to watch.

To step through one line of code, you press F10. For each step, you can inspect the variables' values and other code elements by hovering over them with the mouse or by using the Autos, Locals, and Watch windows. If the stored procedure returns a result set, you can view it in the Output window after you've stepped through all the code.

When you're debugging a T-SQL stored procedure that calls another T-SQL stored procedure, you can press F11 when stepping through the line of code that makes the call to automatically go to the other stored procedure. At that time, you can press F10 to step through the code of the stored procedure being called. Another way to check a stored procedure that will be called is to open that stored procedure in the Visual Studio 2005 development environment and set a breakpoint in it.

Debugging Is Easy Once It's Demystified

As you've now seen, Visual Studio 2005's Database project provides easy-to-use graphical tools to help you create T-SQL stored procedures. Visual Studio 2005 also lets you easily debug those T-SQL stored procedures. You just need to know how to access the debugging options.

Learn more from "SQL Server 2008’s T-SQL Development and Debugging Features."