Sunday, 6 April 2014

Debug Stored procedure sql

How to debug stored procedures in SSMS (Sql server management studio)

How to debug stored procedures in SSMS (Sql server management studio)

To debug a stored procedures or functions in SSMS (sql server management studio 2008 r2) 

Step 1: Create stored procedure which you want to debug. Suppose I have created following stored procedure:

CREATE PROC TestDebug(
@MaxLimit AS INTEGER
)
AS

DECLARE @Count AS INTEGER

SET @Count = 0

WHILE @Count < @MaxLimit BEGIN

SELECT @Count 
SET @Count = @Count + 2
END

Step 2: To debug this stored procedure, write an execute procedure statement and add breakpoints. As shown in the following figure:




Step 3: Click on debug button or press Alt + F5. Debug button has shown in following screen by read oval.



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



Step 5: Repeat the step 4 to go to the next line. Also at bottom in the Locals window you can get values and data type of the local variable.

Note: In debugging mode we can edit the value of variables.  

No comments:

Post a Comment