Debugging Stored Procedures

One of the most powerful tools exposed by the Server Explorer is the ability to step through an executing stored procedure. I call this T-SQL debugging. This technology has been around in Visual Basic and Visual Studio for some time now, but with a very checkered career. As the story goes, Drew Fletcher (a Visual Basic program manager) was walking near Times Square in New York when a man tried to get his attention from the shadows of a dark alley. "Hey, buddy!" he whispered in a gravely voice. "Youse want a way to debug stored procedures from Visual Basic?" Drew kept walking. He had heard about the nuts that hang out in the alleys off of 42nd Street and didn't need any extra adventure in his life. "Don't believe me?" the man persisted as he tailed Drew down the street close behind. "Beat it!" Drew half-shouted as he glanced over his shoulder. Fortunately, he recognized the stranger at oncehe was a Microsoft Consulting Services engineer that worked in the local office. To make a long story short, the MCS people in New York had been able to create a working prototype of a Visual Basic application that could actually debug and step through a stored procedure. What they didn't tell Drew is that this magic required DDE^[3] to work. Over the years, the protocol changed as often as some engineers change their socks (or more frequently). That was the big problemgetting SQL Server to permit an attached development system to interact with the execution of the stored procedure without killing the system or other production work in progress.

^[3] DDE: A low-level (and highly unstable) interop protocol used by Windows from the earliest days.

Today remote debugging has been implemented with yet another protocol. Hopefully, it works better than previous versions. Given that it has taken several wasted hours before finally being able to get it to work, I'm not so sure they have made much progress. At this point, some developers wish that Drew had kept walking and visited that guy up 42nd Street with the coat lined with "authentic" Rolex watches.

T-SQL Debugging Against a Local Instance

When T-SQL debugging works, being able to set breakpoints in stored procedures can dramatically improve your chances of figuring out what your stored procedure is and is not doing. The problem with this technology is that it can be especially hard to set up unless you're running Visual Studio 2005 on the same system as the SQL Server executing the stored procedure. To make things as clear (and safe) as possible, I'm going to try to lead you through this snake- and gator-infested swamp, so stay close together. Incidentally, T-SQL debugging is available only on the Professional or Team editions, so if you're working with the Express or Standard editions, you can only press your nose to the glass and watch.

Tip

Debugging Stored Procedures is available only on Professional and Team editions.

As I said, if you're trying to debug a stored procedure running on a local SQL Server (an instance running on the same system as Visual Studio 2005), it's easy (easier) to debug your stored procedure. Let's step through the process:

1.	From the Server Explorer, select the stored procedure to debug. In this example, I'm debugging "AuthorByYearBorn"it's one of the stored procedures included in the sample Biblio database (see Figure 5.13). Right-click on the stored procedure and choose "Step into Stored Procedure". This opens the now familiar "Run Stored Procedure" parameter capture dialog, shown in Figure 5.13. Figure 5.13. Running a stored procedure with parameters displays the parameter capture dialog.
2.	When you click "OK", Visual Studio opens an interactive T-SQL debug window. Note the debug cursor stops at the top of the first block of executable T-SQL code (see Figure 5.14). Each block delineates a single statement of T-SQL codeeven though it might span several lines. Visual Studio highlights the block about to be executed (as with normal breakpoint debugging). The debugger steps through the procedure block by block (statement by statement), as expected. Figure 5.14. Interactively stepping through T-SQL code. Once execution stops, you can hover over any of the T-SQL declared variables or parameters to see (or change) their current value. You can also hover over SQL Server-managed values (such as @@rowcount), but these can't be changed. As shown in Figure 5.15, I'm hovering over the @YearLow variable and Visual Studio reported its value (1940the value passed from the input parameter dialog). I then clicked on the value shown and changed it to 1939. As I step forward in the logic, this new value is used. Figure 5.15. Any local variable (including input or output parameters) can be altered. Note that if the value set in an input parameter is NULL, Visual Studio shows an ellipsis "{...}" as the parameter value. No, you can't change the T-SQL code (and continue execution), and you can't move the current execution pointer in a T-SQL debug session. Even with these limitations, it's still a useful toolat least, for simple SPs.
3.	To continue stepping through the code, press the keyboard shortcut assigned to Debug.StepInto. This instructs Visual Studio to step to the next code block. In Visual Basic, this key is F8 or F11. In C#, this key is F11.
4.	To continue the stored procedure execution to the end, press Debug.Start (F5) or click the green arrow in the Debug toolbar. Yes, T-SQL debugging works pretty much like a typical Visual Studio code-debugging session.

Debugging with Breakpoints Within Stored Procedures

Before you start debugging more complex stored procedures (those with more than a couple blocks of code), you're going to want to set one or more breakpoints. Just as when setting breakpoints in Visual Basic .NET or C# code, Visual Studio stops execution at the top line of the stored procedure code block containing the breakpointassuming you're not executing the application with the "Execute" option as previously described.

To get execution to stop on a breakpoint, you'll need to execute the stored procedure using ADO.NET in debug mode or interactively in Visual Studio using the "Step Into Stored Procedure" option. While Visual Studio 2005 has several new features that make it easier to debug stored procedures, it's still missing some important functionality, but I'll talk about that a bit later.

Setting a breakpoint is as easy as setting code breakpoints in Visual Studiosimply click on the left (gray) margin next to the point where execution should be paused. Breakpoints are indicated by a red ball in the margin, as shown in Figure 5.16.

Figure 5.16. Setting a breakpoint in a stored procedure.

If you set a breakpoint in a stored procedure, the breakpoints are persisted with the Visual Studio IDE, so when that stored procedure is reopened, those breakpoints are restored.

For this exercise, I'm going to set T-SQL breakpoints in a stored procedure from the Biblio database (ComputeAvgPriceByPublisherAndType), which computes average book price by publisher and state.

1.

Using the Server Explorer, open a connection to the Biblio database, drill into the target stored procedures in the Biblio database, choose ComputeAvgPriceByPublisherAndType, right-click, and choose "Open".

2.

Choose the point to set the breakpoint and left-click in the gray area to the left of the line numbers. This sets a "standard" breakpoint as shown in Figure 5.16. Note that each defined code block (T-SQL statement) supports one (and only one) breakpoint. In my example, while I set the breakpoint at the COMPUTE statement (line 16), Visual Studio moves this breakpoint to the top of the code block (line 8). Additional breakpoints in the same code block are ignored. Once the breakpoint is set, Visual Studio and SQL Server interact to stop execution when the logic reaches the top of the code block (beginning of the T-SQL statement) when executing the stored procedure. Yes, this means if you execute this stored procedure from an application (in debug mode), Visual Studio reopens this window and permits you to step through the stored procedure as it executes. I'll show you how to do that later in this chapter.

Setting Multiple Breakpoints

Let's take a look at another aspect of step-through debugging. Set additional breakpoints on the stored procedure as shown in Figure 5.17.

Figure 5.17. The CompteAvgPriceByPublisherAndType stored procedure after a few additions.

In this example, I set breakpoints on lines 9, 21, and 23. Now when I choose "Step Into Stored Procedure", code execution pauses at line 9. When I press F5, execution pauses again at line 21. While paused, I can hover over any of the input parameters or local variables (such as @CustomerCount) to see the current values.

1.	When paused on the line 9 breakpoint, hover over the "@CompanyWanted" parameter. This opens an intellisense popup that includes the value, as shown in Figure 5.18. By clicking on the magnifying glass icon, you can choose which "visualizer" is to be used to display the data. If the data is complex, these can be a handy way to inspect the value. In this case, I set the @CompanyWanted parameter to "Beta". Figure 5.18. Hovering the mouse cursor over a variable exposes the current value.
2.	Click on the value ("Beta", in this case) and edit the value to change the current value of the selected variable. Moving the mouse cursor away from the variable closes the intellisense popup.
3.	Press F5 to step to the next breakpoint. Hover over the other variables to inspect the current values. Note that global variables (those beginning with "@@") cannot be changedthey're generated by SQL Server.

At this point, I've shown you how to step through a T-SQL stored procedure and set breakpoints on stored procedures running on a local instance of SQL Server. I'm ready to show you how to do the same from a "remote" (non-local) SQL Server instance.