Debugging Stored Procedures in Visual Studio 2005

This article walks through the basics of debugging stored procedures using Visual Studio 2005. It covers breakpoints, watches and stepping through code. It was written by Chris Rock and includes his very unique sense of humor.

One of my favorites activities while working is debugging. A little weird yes, but I’ve always had an affinity towards taking things apart and putting them back together. I haven’t always had success. As a kid I destroyed many toys and pieces of furniture (office chairs) just to find out how they work. I am a curious person by nature and like to figure out how something works OR why something isn't working. Alright, enough about my lame personality.

Before I was enlightened to this world of debugging stored procedures I used the archaic "print" statements to debug stored procedures. When I found out that I didn't have to do that anymore I cried tears of joy. Ok... I didn't actually "cry" but I did mutter something like "That's cool..." (I told you I was lame!) That was when Query Analyzer added the ability to debug stored procedures. Debugging stored procedures has come a long way since the early versions and I'd like to show how to accomplish this very cool feature in Visual Studio 2005.

I suggest that you take a look at http://msdn2.microsoft.com/en-us/library/s4sszxst(VS.80).aspx to verify that you meet the minimum requirements to debug on your SQL server. If you have control of your SQL server box (or SQL is running locally) then you're in good shape and can make any necessary changes. If you're not in control I suggest you buy something for your favorite DBA from thinkgeek.com to get in their good graces and have them assist you in anyway they can! (Note: This probably won't work)

Without further ado let's get started.

The first thing you want to do is add a data connection in the Visual Studio 2005 Servers windows. Right click on "Data Connections" and click on "Add New Connection":

Enter the required connection info in the "Add Connection" dialog and click "Test Connection" to test the connection. If that succeeds, click OK:

After you add the data connection you will be able to see the connection in the Data Connections tree:

We're going to debug "usp_contact_add" in this example (any proc will do, it really doesn't matter what proc YOU use). Drill down until you can see the proc you're debugging under the "Stored Procedures" folder:

Right click on the proc and click "Open". You will see the proc code in the code window:

"Now we debug"

Now that we've figured out how to open the proc we're going to get into actually debugging it. Right click on the proc and click "Step Into Stored Procedure":

A window with all of the parameters for the proc will pop up:

Notice it says "The stored procedure ... REQUIRES the following parameters:" That means you are REQUIRED to fill in the values. Otherwise the debugger will quit right away. That said; click the OK button to step into the procedure. The debugger will automatically stop execution on the first line of code in the stored procedure. When the debugger has stopped execution you'll see a yellow arrow to the left of the line of code:

Depending on how you have your Visual Studio settings you will click either F8 (VB) or F11(C#) to go to the next line of code. The debugger will execute the previous line of code and move to the next line:

Now "nocount" is "set" to "on". Click F8 or F11 again and the insert statement (or whatever line of code the yellow arrow is pointing) will be executed.

Ok... So can we doing anything except watch the debugger work?

Yes, in fact there are several things you can do but I'm going to concentrate on what I use most in VS2005 while debugging stored procedures.

While you're debugging you can view / edit the value of the variables in scope using a few different methods. The first way to view/edit a variable is simply hovering your mouse over the variable:

 

Ref: http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005