Mar 9, 2013

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.

8 comments:

  1. Nice one. I am using this one. We can debug in Visual Studio also.

    ReplyDelete
  2. such a simple code & very easy to understand for the beginner

    ReplyDelete
  3. Easy and simple steps. Useful post for me.

    ReplyDelete
  4. Very nice one that i was finding form last several month . But i have a Question how can i view what value in any variable in sp

    ReplyDelete