How to debug SP procedure from SSMS

Sudip Bhatt 2,271 Reputation points
2020-12-09T11:01:09.817+00:00

My sql server database is Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

i want to debug SP line one by one which was possible before but now not getting this option. it is very hectic to debug a big SP code with PRINT.

can you guide me how to debug SP without PRINT?

any 3rd party tool exist which help me to debug the SP code line one by one just like code debugging Visual studio.
tell me few 3rd party tool which allow me to do this.

thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
Accepted answer
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2020-12-11T02:12:10.653+00:00

    Hi @Sudip Bhatt ,

    > I have VS 2019 community Ed.

    If you really have VS 2019, you can go to menu Tools-> Get Tools and Features.
    Then select SQL Server Data Tools under Data storage and processing.

    47122-01.jpg

    > I do not have VS 2017 in my pc.

    You can use SSDT in Visual Studio 2019.
    Or you can download Visual Studio 2017 from here.

    > please guide me things are not getting clear how to do it.

    After Installing SSDT with Visual Studio 2019, Please go to menu Tools->Connect to Server/Connect to database. Then click on SQL Server Object Explorer to see you Database.
    47135-02.jpg

    To debugging SP, go to database->Programmability->Stored Procedures-> right click the procedure you want to debug->select Debug Procedure.

    47123-03.jpg

    For more details, please refer to this article.
    Or you can follow the steps from this doc.
    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rob Ainscough 26 Reputation points
    2022-12-07T00:59:54.557+00:00

    I added the necessary components to VS 2019 and I still do NOT get an option to "Debug Procedure"?

    I have dbForge Studio and if I try to debug a stored procedures "Failed to load debugger..." error.

    SSMS version is 18.2.1

    Tried VS 2022 and still not ability to debug/step thru my T-SQL code ... I know this is old thread, but still unable to debug.

    1 person found this answer helpful.
  2. Olaf Helper 41,001 Reputation points
    2020-12-09T12:24:19.297+00:00

    AFAIK the feature to debug a stored procedure in SSMS was removed, but you can use SSDT = "SQL Server Data Tools" to debug a SP, see https://learn.microsoft.com/en-us/sql/ssdt/how-to-debug-stored-procedures?view=sql-server-ver15

  3. Yitzhak Khabinsky 25,116 Reputation points
    2020-12-09T13:32:18.81+00:00

    So far, last supporting inline debugging SSMS version is 17.9.1
    You should be able to install that SSMS version from here: ssms 17.9.1

    Additionally, there is a request/feedback to restore debugging in latest SSMS version. Please go ahead and up-vote it here: put-debugger-back-into-ssms-18

    Same with your favorite topic. Please up-vote: sql-server-vnext-post-2019-and-nosql-functionality