CODEWITHSUNDEEP
sql-serverdebuggingstored-procedures
Unknown
Unknown

Reputation: 191

How to debug stored procedures in SQL Server 2018

How can we debug stored procedures in SQL Server 2018? As SQL Server 2018 and above versions do not have this option. Debugging makes life really easy. I have been trying to solve one problem for past one hour and if I could debug my stored procedure, I could have definitely save a lot of time. I did search a lot and did not find anything useful that could work.

Please do let me know if there is any way. Thanks.

Upvotes: 0

Views: 14810

Answers (1)

Dai
Dai

Reputation: 155250

How can we debug stored procedures in SQL Server 2018?

There is no such product as "SQL Server 2018" - I assume you're referring to one of the following:

  • SQL Server 2017
  • SQL Server 2019
  • SQL Server Management Studio 18

If you're referring to SQL Server 2017 or SQL Server 2019 then you can debug Stored Procedures by either:

  • Using SQL Server Management Studio 17.
    • Which may not work when connecting to a SQL Server 2019 instance, so YMMV.
  • Using SQL Server Data Tools (aka SSDT).
    • This is currently the officially supported tool for debugging SQL Server procedures and other objects in SQL Server.
    • SSDT is included in the free-as-in-beer Community Edition of Visual Studio without restrictions on its use in commercial settings.
    • But SSDT is not in any way a replacement for SSMS: SSDT only has basic tools for administering running SQL Server instances. So you'll need both SSMS and SSDT installed side-by-side.

If you're actually referring to SQL Server Management Studio 18 then you cannot debug procedures in SSMS 18 because Microsoft removed the feature for reasons unknown - I suspect perhaps involving the usual vague business reasons the veeps hand-wave with - because I cannot fathom any seriously good reason (though I agree the tooling was clunky, it did work at least).

To debug a procedure with SSDT you need to install Visual Studio (2017, 2019, they all have it) and select SSDT during VS installation. In previous releases of VS (like 2012, 2013, and 2015) SSDT used to be a separate download for VS but since VS 2017 it's part of the main installer.

When VS opens, dismiss the Start Screen and get to the main window, and go View > SQL Server Object Explorer, then connect to your server and the rest should be familiar to you.

As it sounds like you already have a substantial database project that exists in an extant SQL Server instance but also that you are not currently using SSDT - so I recommend you take this opportunity to actually move your project out of SQL Server and into an SSDT *.sqlproj - that way you can manage your database's design with source-control (Git, etc) and do local "builds" of your database (which will verify your code is valid T-SQL, it's saved me a lot of stress and trouble since I started using SSDT over 12 years ago) - it also makes publishing, deployments and schema changes a breeze (except for the fact the SSDT seems to get buggier and develops new glitches with each new release, ugh).

Upvotes: 2

Related Questions