Problem:When we run in 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 inside it, what you do?
Then for 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 sometime 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. That is "TSQL Debugger".
With this feature we can debug the stored procedure and can track or watch all the thing inside logic. Let's look on some shots which i have captured during debugging of the stored procedure. Create this 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 procedure , select the second script to debug it. After selecting press F11, screen will open sored procedure in debug mode as below.
You can see in the image as how can we add Watch of the variables while debugging. From below image you have idea of how values are tracked for variables.
Finally with completion of debugging the stored procedure the last output variable selected.
Happy debugging!