question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked Criszhan-msft answered

How To Troubleshoot A Stored Procedure

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered InigoMontoya-1790 commented

Hi @InigoMontoya-1790,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How do you use the inline T-SQL debugger ? I work with SSMS, and have never used this.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered InigoMontoya-1790 commented

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'
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

using a variable for debugging an dprinting when if/needed, I like it.

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.