Visual Studio is a great tool for developers, and it offers so many great features, such as IntelliSense, unit testing and code analysis. One of the great and very useful features is debugging. It is possible to debug managed code and do things like adding breakpoints, stepping into modules and watching values of variables and objects.

But it is less known that it is also possible to debug stored procedures from Visual Studio with almost the same functionality. The problem is that it doesn’t work out of the box, and there are a few steps you should take (only once) in order to enable debugging of stored procedures from your Visual Studio project.

The goal of this post is to list these steps and get you up and running as quickly as possible, so that you can seamlessly debug your managed code and your stored procedures by stepping from one to another.

I’m using Visual Studio 2013 and SQL Server 2014 for the purpose of this post. If you are using different versions, then some of the instructions in this post might be a bit different for your environment, but the general idea is the same. If you follow the instructions in this post, it shouldn’t be too hard to apply them to your environment as well.

Ready? Let’s go…

First, make sure that you have the SQL Server Database Tooling installed. With this tooling you can create SQL Server projects in Visual Studio and leverage a lot of great functionality, such as schema and data compare, database unit testing and code refactoring. In Visual Studio 2013, go to Tools -> Extensions and Updates, and locate it under “Product Updates”.

Click on “Update”, and follow the instructions. Once the setup is complete, you’ll be able to create SQL Server projects in Visual Studio, develop managed code and stored procedures in the same environment, and leverage so many great features. Have fun!

If you already have the database tooling installed, it might not be the latest version, and it might not support SQL Server 2014. If you open SQL Server Object Explorer and try to create a new SQL Server connection, and the “Connect to Server” dialog looks like this:

As you can see, this dialog includes the logo of SQL Server 2012, which means it’s not even aware that there is a newer version, because it’s too old. It uses the SQL Server native client for SQL Server 2012, and it doesn’t support SQL Server 2014. If you try to continue and connect to a SQL Server 2014 instance, you’ll get the following error message:

In this case, install the latest update of the database tooling from the “Updates and Extensions” window, as described earlier.

Now, if you try to add a SQL Server connection again, you’ll see the following dialog:

Much better! Now you can connect and work with SQL Server 2014 from Visual Studio. Congratulations!

Let’s move on…

Now, I’m going to create a sample database with a sample stored procedure. Here is the script:

USE
master;
GO
CREATE DATABASE
DebugTest;
GO
USE
DebugTest;
GO
CREATE PROCEDURE
dbo.DoStuff
(
@InputParameterAS INT ,
@OutputParameterAS INTOUTPUT
)
AS
PRINT N'Beginning Execution...';
SELECT
ObjectId= object_id ,
ObjectName= name
FROM
sys.objects;
SET @OutputParameter = @InputParameter * 2;
PRINT N'Ending Execution...';
GO

This stored procedure doesn’t do much, but it does enough to showcase debugging capabilities. It has an input parameter and an output parameter (which is set to twice the input parameter), and it also includes PRINT statements and a SELECT statement.

Next, I’m going to create a new SQL Server Database project (C#), add a button, and in the button click method, put the following code:

private void button1_Click(object sender, EventArgs e)
        {
            string connectionString =
                "Data Source=.\\SQLSERVER2014;Initial Catalog=DebugTest;"
                + "Integrated Security=true";
            int paramValue = 5;
            using (SqlConnection connection =
                new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand("dbo.DoStuff", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@InputParameter", paramValue);
                SqlParameter outputParameter = new SqlParameter("@OutputParameter", SqlDbType.Int);
                outputParameter.Direction = ParameterDirection.Output;
                command.Parameters.Add(outputParameter);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("\t{0}\t{1}",
                            reader[0], reader[1]);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                Console.ReadLine();
            }
        }

This piece of code declares a connection string and a local variable (“paramValue”), which is set to 5. It then declares a stored procedure command along with its parameter collection. Finally, it declares a data reader, executes the stored procedure and outputs the contents of the data reader. You, should, of course, modify the connection string to match your environment. Make sure that your project builds and runs successfully.

You can add breakpoints to your C# code and debug your application, but at this point you still can’t step into the stored procedure. In order to be able to do that, you need to enable SQL Server debugging. Yes, there is a checkbox for that, and it’s not enabled by default. Go to the project properties window, then to the “Debug” section, and check the “Enable SQL Server debugging” checkbox at the bottom of the screen.

Now, in order to step into the stored procedure, you first need to add a breakpoint somewhere in the code before the call for the stored procedure. For example, set a breakpoint on the following line of code:

SqlDataReader reader = command.ExecuteReader();

This breakpoint will allow you to catch the execution before the stored procedure call, so that you can step into the stored procedure. But if you try to step into the stored procedure, it will actually step over the stored procedure and won’t even open the window with the stored procedure code.

You need to also add a breakpoint somewhere inside the stored procedure. Find the stored procedure in the SQL Server Object Explorer or in the Server Explorer and open it. Then set a breakpoint on some line of code – for example: the SELECT statement.

Now you can finally step into the stored procedure and watch its execution step by step. You can use the Locals and Watch windows to watch the values of parameters, variables and objects within your application code as well your stored procedure code. For example, you can watch how the input parameter is passed from the application to the stored procedure, how the output parameter gets its value inside the stored procedure, and how the output parameter is passed back to the application.

When you get to the last line of code in the stored procedure and step into the next line (in the application), execution continues to the end of the application code. In order to be able to step out of the stored procedure and continue to step through the C# code, you should add another breakpoint somewhere after the execution of the stored procedure.

Finally, there are some limitations to debugging stored procedures that you should be aware of:

• You can’t edit your stored procedure code and continue to run while debugging.
• You can’t change the order of statement execution.
• The output of PRINT statements is not displayed in the Output window.

Happy debugging!

The post Debug Managed Code and Stored Procedures Seamlessly appeared first on .