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.

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

 

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

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

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

 

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…

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

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

 

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.

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

 

Step 3:

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

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

 

Step 4:

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

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

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

 

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.

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

 

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

 

 

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.

Posted in SQL
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments