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:
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!
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 @outAfter 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!
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
ReplyDeleteAlso, 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