How to debug stored procedures in SQL server management studio

SQL Server 2008 introduced "TSQL Debugger" feature to debug the stored procedures. This allows tracking or watching the things inside logic.

In this article, I am going to demonstrate the steps to debug the stored procedures in SQL server management studio

To Begin, Create stored procedure which you want to debug. Here I have created following stored procedure:

CREATE PROCEDURE  [dbo].[SP_TestDebug]

@Input1  INT,

@Input2  INT,

@Output INT out

AS

BEGIN

     

         IF ( @Input1 IS NULL ) SET @Input1 = 0

         IF ( @Input2 IS NULL ) SET @Input2 = 0

        

         SELECT @Input1 + @Input2 as Count

END

 After creating stored procedure, write an execute procedure statement and add breakpoints.

DECLARE @Result INT

EXEC SP_TestDebug 10, 20,@Result OUTPUT

         

After that, click on debug button or press F11 / Alt + F5.

To go next lines press F11 key or click on step into button. Control will automatically move to code of stored procedure as shown in the following figure:

        

SQL server management studio allows to edit the value of variables in debugging mode.