How To Troubleshoot A Stored Procedure

Inigo Montoya 586 Reputation points
2021-04-09T15:20:11.547+00:00

What are some tips and tricks on how to troubleshoot a stored procedure?

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,485 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,831 Reputation points
    2021-04-09T15:38:32.937+00:00

    Hi @Inigo Montoya ,

    There are two main methods:

    1. By using PRINT statement in the strategic locations in a stored procedure.
    2. By using inline T-SQL debugger (SSMS 17.9.1 and earlier, or Visual Studio)
    1 person found this answer helpful.

  2. Tom Phillips 17,716 Reputation points
    2021-04-09T17:12:13.107+00:00

    I use a method in most of using PRINT statements around a variable @debug. Then you can turn it on and off when needed.

    CREATE PROC ...
    @debug int = 0
    AS
    
    IF @debug >0
    PRINT 'stuff'
    

  3. Cris Zhan-MSFT 6,601 Reputation points
    2021-04-12T08:52:57.807+00:00

    Hi,

    The Transact-SQL debugger feature works with SSMS version 17.9.1 and earlier, it was removed from SSMS version 18.0. But you can install and use SSMS 17.x and SSMS 18.x side by side.

    The demand for the debugger to be put back to SSMS 18 is very high on the SQL server use feedback, second only to the dark theme.

    Also have a look on this similar case.
    How to debug tsql code in Visual Studio since SSMS v18 does not have a debugger

    0 comments No comments