You are here:Home » variable » TSQL Debugger for Stored Procedure - SQL Server 2008

TSQL Debugger for Stored Procedure - SQL Server 2008

Problem:
When we run into the issue of something wrong or incorrect with stored procedure and suppose the stored procedure has 1000 lines long and very complex code for custom logic in it, what you do? Then from above such issue, we put the SQL trace and get the procedure parameters and manually run the stored procedure and put the print statement inside SP to watch parameter track. Or sometimes we run the each block manually one by one sequentially to get the issue point.

Solution:
After releasing of SQL Server 2008, We have one more option or feature to get it easily done for the issue. Thais it "TSQL Debugger". With this feature we can debug the stored procedure and can track or watch all the thing inside logic. Let's look for some shots which I have captured during debugging of the stored procedure. Create a store procedure,

Example:
CREATE PROCEDURE Procdebug @in  INT,
                           @out INT out
AS
  BEGIN
      IF ( @in IS NULL )
        SET @in = 0

      WHILE ( @in < = 100 )
        BEGIN
            SET @out = @in
            SET @in = @in + 10

            PRINT CONVERT(VARCHAR, @in) + ' - ' + CONVERT(VARCHAR, @out)
        END
  END

GO 


DECLARE @out INT
EXEC Procdebug 1,@out OUTPUT
SELECT @out
After creating stored procedures, select the second script to debug it. After selecting press F11, a screen will open stores procedure in debug mode as below,


After creating stored procedures, select the second script to debug it. After selecting press F11, a screen will open stores procedure in debug mode as below

Finally, with completion of debugging the stored procedure the last output variable selected.


Happy debugging!