Debugging T-SQL using Visual Studio

[erinstellato]

Hey folks - I've seen some comments and replies stating that folks cannot use the debugger with SSDT in Visual Studio, so I decided to give it a try to see if there were any issues. Good news, it works! In this post we'll step through how to use the debugger with SSDT and Visual Studio, and I'll call out up front that it requires more permissions than you might realize.



To get started, install Visual Studio, and the SQL Server Data Tools – Sql Editor (SSDT) extension if you don't already have them. If you do not have a license for Visual Studio, that’s ok, you can install Community edition!

Setup​

With Visual Studio and SSDT installed, find a development or test server that you can use for initial testing. I do not recommend working in a production environment. All examples here use a local installation of SQL Server 2022 to prevent breaking anything for anyone else.

The first step is to connect to the instance and a test database and create a few objects.



Screenshot of creating a table and stored procedure in Visual Studio with SSDT



CREATE TABLE [dbo].[SampleProducts] (

[ID] INT IDENTITY (1,1),

[ProductName] NVARCHAR(100)

);

GO



CREATE PROCEDURE [dbo].[AddSampleProduct] @Name NVARCHAR(100)

AS

BEGIN

INSERT INTO [dbo].[SampleProducts] ([ProductName])

VALUES (@Name)

DECLARE @NewID INT

SELECT @NewID = ID

FROM [dbo].[SampleProducts]

WHERE [ProductName] = @Name

DECLARE @CharID NVARCHAR(10)

SET @CharID = CAST(@NewID AS NVARCHAR(10))

INSERT INTO [dbo].[SampleProducts] ([ProductName])

VALUES (@CharID)

END;

GO

Debugging​

Now we can start debugging. In a new editor window, put a debug breakpoint next to the T-SQL statement of interest, and then initiate debugging via the Execute with Debugger icon (I added it to the toolbar in the screenshot below), or ALT + F5, or SQL -> Execute with Debugger. For this example, I started debugging with @StartDebug variable declaration to show stepping into a stored procedure.



Screenshot of starting to debug T-SQL in Visual Studio with SSDT



With the debugger started we can step into the code, allowing us to see the current values for any parameter:



Screenshot of debugging T-SQL with variable values in the Locals window



Another step in drops us into the AddSampleProduct stored procedure, opening another editor on the right:



Screenshot of T-SQL debugging after stepping into a stored procedure



A few more steps and all related variables are populated. As part of testing, you can edit values while debugging:



Screenshot of all variables in Locals window while debugging



This example illustrates how to use the T-SQL debugger in Visual Studio for SQL Server, and while simple, it encapsulates the typical scenario of how folks used the debugger in SSMS 17 and below: to step through code.

Important notes about debugging​

There are several factors to keep in mind when debugging:

• Debugging can cause blocking or other unexpected scenarios that affect performance. Do not debug in production.
• This will only work for SQL Server on-premises.
• If the account you’re connected with, or the SQL Server service account, doesn’t have enough permissions, you’ll get errors.

The article Configure firewall rules before running the Transact-SQL debugger has a list of requirements at the end, as well as instructions for configuring firewall rules. If you run into issues, that article is a good place to start with troubleshooting. The main documentation for debugging in SSDT starts with the Transact-SQL debugger article.

Conclusion​

As discussed in the SSMS Roadmap post, there are no plans to bring this debugger back to SSMS. For folks who still need this functionality, it exists in Visual Studio (Community edition is free if your company does not have a subscription). Hope this helps!

Continue reading...