0

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.

Improve this question
5
  • 2
    There is no SQL Server 2018, presumably you mean SSMS, which is not SQL Server. Debug using print to output intermediate results. Commented Oct 30, 2021 at 14:03
  • 1
    sql server 2018?? There is no such thing. The latest version is 2019, and the version prior to that was 2017. But regardless of the version of SQL Server you're using (real or not) you want to look at the SSDT extension for Visual Studio for debugging procedures. Commented Oct 30, 2021 at 14:03
  • Run SELECT @@VERSION in SSMS to get the actual SQL Server engine version you're using - please post results here. Commented Oct 30, 2021 at 21:15
  • Use SSDT, not SSMS, to debug stored procedures in SQL Server. Commented Oct 30, 2021 at 21:17
  • @Stu PRINT doesn't let you inspect table-value-variables, and PRINT output is delayed until after a statement batch is completed, making it useless if you're using WHILE or CURSOR, instead you should use RAISERROR (sic) with NOWAIT and a non-error severity to get real-time updates on the procedure or batch. Commented Oct 30, 2021 at 21:32

1 Answer 1

Reset to default
2

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.