Debugging Stored Procedures in Visual Studio 2005

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:

The next way to view/edit the variable is using watches. The first type of watch is the "Quick Watch". A quick watch simply shows you the value in its current state. Right click on the variable and click "Quick Watch":

The quick watch window will be displayed:

The second type of watch is the "old school" watch using the "Add Watch" option. Right click on the variable and click "Add Watch". Clicking "Add Watch" will add the variable to the watch window. If you don't see the watch window press CTRL + D, W to bring it into focus.

The third way of viewing / editing the variables is the locals window...my favorite! (CTRL + D, L). This window will list all of the variables and their associated values:

If you're feeling adventurous you might give the immediate window (CTRL + D, I) a try.

I love breakpoints, you love breakpoints, If we didn't use breakpoints, we'd be lame.

(Note: The above title is set to the "Cheesy Poofs" theme song from South Park episode 211)

Another cool feature while debugging procedures is breakpoints. If you don't want to deal with stepping through each line of code to get to the problem code you can add a breakpoint. A breakpoint will stop execution at the line of code the breakpoint is set on while the debugger is active. This is invaluable when debugging a really large procedure or the last DBA's code. We all know the last DBA didn't know what they were doing!

To add a breakpoint click on the line of code where you want the breakpoint and press the F9 key. After you press F9 you should see a red "ball" to the left of code where the breakpoint was set:

Now we want to execute the procedure and hit our breakpoint. Right click the code window and click "Step Into Stored Procedure". Again, fill in the data in the parameters window and click OK. The debugger will again stop on the first line of code. Click F5 to tell the debugger to start running again. The debugger will now stop execution at the breakpoint:

There you go. Getting through all of the code you don't want to execute manually is as easy as pressing F9 (<< That sets the breakpoint).

(Note: Pressing F5 while the debugger is active will execute the stored proc until it: 1) hits a breakpoint. 2) Exits the proc.)

Get return value from stored procedure

This article discusses three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.  Each example includes client-side code (ASP.NET) and server-side code (T-SQL) to read the results.

Result Sets

Result sets are what you get when you run a simple SELECT statement inside a stored procedure. Let's suppose you want a stored procedure to return a list of all the people with a given last name.  The code for the stored procedure might look like this:

CREATE PROCEDURE GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
    FirstName,
    LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID

If you just execute this stored procedure in SQL Server Management Studio you get a result set that looks like this:

EXEC GetPeopleByLastName @LastName = 'Alexander'

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

  ContactID FirstName                           LastName
----------- ----------------------------------- -----------------------
         22 J. Phillip                          Alexander
         23 Michelle                            Alexander
        430 Mary                                Alexander
        
 . . . { hiding a bunch of rows } . . .
 
      19942 Morgan                              Alexander

(123 row(s) affected)

 

If you want to write a web page that calls this stored procedure and processes the results that code you'll need to add a using clause for the SqlClient namespace.  This is needed for all the client side samples.

using System.Data.SqlClient;

The code itself might look like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.GetPeopleByLastName"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));
        conn.Open();
        cmd.Connection = conn;
      
        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
            {
                int contactID = rdr.GetInt32(rdr.GetOrdinal("ContactID"));
                string firstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
                Response.Write(firstName + " (" + contactID.ToString() + ")");
            }
            rdr.Close();
        }
    }
}

If you want to capture this result set using T-SQL you'll need a place to store it. Temporary tables work well for that.  That code might look something like this:

DECLARE  @People TABLE (
    ContactID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
)

INSERT @People (ContactID, FirstName, LastName)
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

SELECT COUNT(*) FROM @People
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

(123 row(s) affected)
           
-----------
        123

(1 row(s) affected)

This code let's us capture the result set from the stored procedure into a table variable or temporary table.  I use code like this when I work with packaged applications.  I call their stored procedures to get the results I want and then manipulate it as needed.

Using OUTPUT variables

If you just want to return a single value (or a couple of values) you can use output variables.  The stored procedure looks like this:

CREATE PROCEDURE dbo.GetCountByLastName (
    @LastName NVARCHAR(50),
    @LastNameCount INT OUTPUT )
AS
SELECT @LastNameCount = COUNT(*)
FROM Person.Contact
WHERE LastName = @LastName

If we want to return the value using T-SQL we'd use code like this:

DECLARE @TheCount INT

EXEC dbo.GetCountByLastName 
    @LastName = 'Alexander',
    @LastNameCount = @TheCount OUTPUT

SELECT TheCount = @TheCount
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

   TheCount
-----------
        123

(1 row(s) affected)

And we can see that there are still 123 Alexanders in our database.  To call run this stored procedure from ASP.NET we'd need code that looked like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.GetCountByLastName"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));

        SqlParameter countParameter = new SqlParameter("@LastNameCount", 0);
        countParameter.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(countParameter);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
        Response.Write("<p>Count: " + count.ToString());
        conn.Close();
    }
}

Using Return

The last way to get data back from a stored procedure is also the most limiting. It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure. Consider this procedure:

CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue
GO

All it does is use the RETURN statement to send back the value that was passed in. Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found. The following script calls the TestReturn stored procedure:

DECLARE @ReturnValue INT
EXEC @ReturnValue = TestReturn 3
SELECT ReturnValue=@ReturnValue

and the output looks like this:

ReturnValue 
----------- 
3

(1 row(s) affected)

Whatever number is returned using the RETURN statement is put into the variable @ReturnValue.  The ASP.NET code to get the return value looks like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.TestReturn"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@Invalue", 3));

        SqlParameter returnValue = new SqlParameter("@Return_Value", DbType.Int32);
        returnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(returnValue);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@Return_Value"].Value.ToString());
        Response.Write("<p>Return Code: " + count.ToString());
        conn.Close();
    }
}

Those are the three best ways I know of to get data back from a stored procedure.