Skip to main content

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!

Comments

  1. You may want to take an in-depth look at my coverage of T-SQL Debugger in the VTD webcast recorded session at: http://beyondrelational.com/blogs/nakul/archive/2011/06/09/vtd-recorded-session-amp-ppt-learn-underappreciated-features-of-microsoft-sql-server-are-you-attending-tech-ed-on-the-road-ahmedabad.aspx

    Also, you should go through the following posts to troubleshoot the T-SQL Debugger and also set it up for advanced debugging:
    1. http://beyondrelational.com/blogs/nakul/archive/2011/05/16/enabling-t-sql-debugger-in-sql-server-management-studio-ssms.aspx
    2. http://beyondrelational.com/blogs/nakul/archive/2011/05/19/t-sql-debugging-connection-errors-amp-firewall-settings.aspx
    3. http://beyondrelational.com/blogs/nakul/archive/2011/05/23/t-sql-debugging-ssms-errors-msdbg2-dll.aspx

    ReplyDelete

Post a Comment