DEV Community

Cover image for How To Debug SQL Procedures and Queries in Microsoft Visual Studio
Njeri Muriithi
Njeri Muriithi

Posted on

How To Debug SQL Procedures and Queries in Microsoft Visual Studio

Stored procedures and functions can be long and tedious to debug.
This article will guide you on how to debug stored procedures or run basic queries using Microsoft Visual Studio Code.

  1. Open Microsoft Visual Studio CodeClick Continue without code.

Microsoft Visual Studio code with an illustration to continue without cod

2. Connection to database

Click on the View tab and select SQL Server Object Explorer from the dropdown.
In the SQL Server Object Explorer, connect to the SQL Server by adding the necessary SQL Server login credentials, and select the desired database for debugging.

SQL Server Object explorer Connection

3 Debugging Stored Procedure

1.View Code

To View the stored procedure, expand the **Programmability **node, right-click on the desired stored procedure, and select View Code.

The stored procedure on Microsoft Visual Studio:
Stored procedure code on view code

The stored procedure on Microsoft SQL Server Management Studio:
Image of Stored Procedure on SQL

2. Execute Procedure

TO execute the stored procedure right-click on the desired stored procedure, and select Execute Procedure

Image of executed stored procedure

3.Debug procedure

To debug a stored procedure, right-click on the desired stored procedure and select Debug Procedure.
This allows you to navigate through the code, step into or step over the procedure, and add breakpoints as needed.

Code showing debugger once you click debug procedure

4.Update procedure.

If you need to add more parameters or modify an existing stored procedure, you can easily test, debug, and then update the procedure in SQL Server.
For example: let's remove the MpesaID and PhoneNumber fields from the stored procedure.

Image of edited code to enable update in the SQL Server

Update the stored procedure in the SQL by clicking update _then _Update Database on the popup and the stored procedure in the Microsoft SQL Server Management Studio receives the changes.

The two fields have now been removed in the SQL as shown below⬇️:

Image of Updated query in SQL

Running SQL Queries

Click on the desired database -> right-click, and select New Query from the dropdown.
This opens a new window where you can write your queries.
Right-click and select Execute or Execute with debugger to run the query.
For example: let's get the total Mpesa balance from the mpesaAccounts table where userId is 1:
👇

Image of SQL query run on Microsoft visual studio code

Conclusion

Debugging stored procedures/functions and writing basic or complex queries has been made easier by connecting SQL Server to Microsoft Visual Studio.

Thank you for reading!❤️.

Top comments (0)