How to Debug Stored Procedures in Visual Studio in 3 Steps

Article summary

Stepping Through Stored Procedures
Invaluable Tools

My first project at Atomic was a C#-based web application using Visual Studio. As time passed, I became familiar with many of the shortcuts and tools that Visual Studio provides to help with common development tasks. Whenever there was a section of code that I didn’t quite understand, I would use the debugging tools to my advantage.

The application relied quite heavily on stored procedures, which I was used to writing within SQL Server Management Studio (SSMS). Unfortunately, SSMS doesn’t provide many tools to help with writing complex stored procedures. Not having much SQL experience beyond basic SELECT, INSERT, and UPDATE statements, I decided to use Visual Studio’s tools to help me out.

Stepping Through Stored Procedures

Before we begin, I would like to clarify that I do not think this method is required in every case. If the stored procedure in question is not very complex, or if you prefer not to use a debugger, then this method is not for you. For those of us who need a little extra help once in a while, here are the instructions:

Step One: Connect to the database.

In order to perform any debugging, you’ll need to establish a connection to the database containing the stored procedure. Within Server Explorer, select the “Connect to Database” option, and fill in the required connection information.

Step Two: Locate the desired stored procedure.

After connecting to the desired database, you will now be able to use Server Explorer to navigate through the different parts of the database. If you already know the setup of SQL Server Management Studio, this will seem quite familiar.

Open up the “Data Connections” section that is now available in Server Explorer, and expand the database where you want to connect. There should be a “Stored Procedures” folder with all of the stored procedures in the database. Open this file, and find the specific stored procedure that you wish to debug. Right-click on the stored procedure and select the “EXECUTE” option. This will open a new query window where you can execute your stored procedure as shown below. If your stored procedure requires parameters as input, Visual Studio will prompt you to enter the values before opening the new query window.


USE [test_db]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[s_My_Stored_Procedure]

SELECT  @return_value as 'Return Value'

GO

Step Three: Execute with debugging.

In the top left corner next to the green arrow, you’ll see a dropdown icon (don’t click the green arrow). Click the dropdown arrow, and select “Execute With Debugger.” This will start executing the stored procedure and allow you to use the familiar debugging options (e.g., Step In, Step Over, Continue, etc.).