Share via

How To Troubleshoot A Stored Procedure

Johnathan Simpson 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 | Other
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 27,111 Reputation points
    2021-04-09T15:38:32.937+00:00

    Hi @Johnathan Simpson ,

    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. Cris Zhan-MSFT 6,676 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
  3. Tom Phillips 17,786 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'

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.