How to Debug Stored Procedures in SQL Server using Visual Studio 2019

In this quick tutorial, we’ll discuss how to debug SQL queries written in Stored Procedures using SQL Data Tools in Visual Studio 2019.

When developing complex Stored Procedures, sometimes a need for some Debugger tools feels out. Before the latest version of SQL Server Management Studio version 18, there was a Debug option available. Using which we can easily add breakpoints to easily debug SQL queries and watch variables.

 

But with the release of latest version 18 of SQL Server Management Studio (SSMS), the Debug option is now removed

 

Solution

They have removed this option, as we can easily connect to SQL Server from Visual Studio and perform Debugging on Stored Procedures after connecting the server.

Let’s check how to enable debugging in Visual Studio.

 

Step 1:

Go to start menu and search for Visual Studio Installer and click on it. Or otherwise, you can also click on Tools>> Get Tools and Features…

 

Step 2:

After opening the Visual Studio Installer, under the Workloads check the Data storage and processing. Then click on Modify. This needs to close all other instances of Visual Studio IDE.

 

Step 3:

After completing the installation open the Visual Studio, then click on Tools then Connect to Server…

 

Step 4:

After connecting to the Server, click on SQL Server Object Explorer to see your Databases.

 

Step 5:

To start to debugging, go to the Procedure you want to debug, then right-click then select Debug Procedure… Then it will enter into debugging mode.

 

 

 

That’s all for enabling and performing Debugging in Visual Studio 2019. Debugging the complex code from existing code can help in better understanding the logic used in comparatively less time.